prucommercialre.com


Rastreando referencias inválidas

Joel señaló que cuando se cierra un libro que tiene miles de fórmulas en las que está recibiendo este mensaje últimamente: "Una fórmula en esta hoja de trabajo contiene una o más referencias inválidas." Joel se pregunta cómo puede saber cuál de los siete hojas de trabajo en este libro se hace referencia. ¿Cómo puedo encontrar la fórmula errante? Yo no observo ningún problema en la visualización de la información en mis informes.

La localización de referencias inválidas puede ser frustrante. Hay varios lugares que usted puede comenzar a mirar. La primera es en las fórmulas que se encuentran en las hojas de trabajo. (Sí, usted tiene que hacer estos pasos para cada hoja del libro.) Utilice el cuadro de diálogo Ir a Especial (presione F5 y seleccione Especial) para optar por ir sólo a las células que contienen errores. A continuación, puede utilizar la tecla Tab para moverse entre las células que selecciona Excel.

También puede utilizar la herramienta Buscar para buscar posibles errores. Sólo tiene que pulsar Ctrl + F para mostrar la ficha Buscar de la Búsqueda y el cuadro de diálogo Reemplazar, a continuación, busque el carácter #. Asegúrese de decirle a Excel para realizar su búsqueda dentro de fórmulas. Inspeccione cualquier cosa que se encontró para ver si se trata de un error o no.

Usted también debe tener una mirada en ningún rangos con nombre definido en el libro. Mire cada nombre en el cuadro de diálogo Definir (Insertar | Nombre | Definir), asegurándose de que lo que está en el cuadro se refiere a no incluir ninguna indicación de error.

Estos no son todos los lugares que no se pueden producir errores; Excel es realmente bueno en dejar que existen errores en muchos lugares. Si necesita buscar errores a menudo, puede probar con una macro que mira a través de las fórmulas de los errores potenciales.

CheckReferences Sub ()
"Consultar por posibles eslabones perdidos o erróneos en
'fórmulas y lista de los posibles errores en una hoja de resumen

ISH Dim As Integer
Dim sShName As String
Sht Dim como hoja de trabajo
C dévil como célula
Dim Schär As String
Rng Dim como gama
Dim i As Integer, j As Integer
Semanas dévil como hoja de trabajo
Dim schr como secuencia, addr As String
Dim sFormula como secuencia, scVal As String
Dim lNewRow como de largo
VHeaders Dim

vHeaders = Array ("Nombre de hoja", "Cell", "Valor de la celda", "Fórmula")
'Si' 'hoja Resumen está en libro
"Y si es así, elimínelo
Con la aplicación
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = XlCalculationManual
End With

Para i = 1 Para Worksheets.Count
Si las hojas de trabajo (i) .Nombre = "Resumen" Entonces
Hojas de trabajo (i) .Delete
End If
Siguiente i

ISH = Worksheets.Count

'Crear una nueva hoja de resumen
Sheets.Add Después: = Sheets (ISH)
Sheets (Sheets.Count) .Nombre = "Resumen"
Con Sheets ("Resumen")
Range ("A1: D1") = vHeaders
End With
lNewRow = 2

'Esto no funcionará si la hoja está protegida,
'Asuma que la hoja no debe ser cambiado; así ignorarlo
On Error Resume Next

Para i = 1 Para ISH
sShName = Worksheets (i) .Nombre
Hojas Application.Goto (sShName) .Cells (1, 1)
Establecer RNG = Cells.SpecialCells (xlCellTypeFormulas, 23)

For Each c IN RNG
addr = c.Address
sFormula = c.Formula
scVal = c.Text

Para j = 1 Para Len (c.Formula)
schr = mediano (c.Formula, j, 1)

Si schr = "[" O schr = "!" O _
EsError (c) A continuación,
'Escribir valores en hoja de resumen
Con Sheets ("Resumen")
.Cells (LNewRow, 1) = sShName
.Cells (LNewRow, 2) = addr
.Cells (LNewRow, 3) = scVal
.Cells (LNewRow, 4) = "'" & sFormula
End With
lNewRow = lNewRow + 1
Salir Para
End If
Siguiente j
Siguiente c
Siguiente i

'Limpieza
Con la aplicación
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = XlCalculationAutomatic
End With

Establecer WKS = Nothing
Establecer sht = Nothing
Rng Set = Nothing

'Poner en orden
Sheets ("Resumen"). Seleccione
Columnas ("A: D") EntireColumn.AutoFit.
Range ("A1: D1") Font.Bold = True.
Range ("A2"). Select
End Sub

Esta macro crea una hoja de trabajo llamada "Resumen" que se utiliza para mostrar información acerca de los errores detectados en los vínculos de hoja de cálculo.

También puede utilizar el programa FindLink de Excel MVP Bill Manville, que hace un trabajo increíble de localización de información en los enlaces. Usted podría utilizar el complemento para buscar el carácter # en todos sus enlaces, que deberían ayudar a localizar los errores. Más información sobre FindLink se puede encontrar aquí:

http://www.oaltd.co.uk/MVP/

ExcelTips es su fuente de capacitación rentable Microsoft Excel. Esta punta (8662) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y versiones posteriores) aquí: Rastreando referencias inválidas.