Joel notó que cuando cierra un libro de trabajo que tiene miles de fórmulas, últimamente recibe este mensaje: «Una fórmula en esta hoja de trabajo contiene una o más referencias no válidas». Joel se pregunta cómo puede saber a cuál de las siete hojas de trabajo de este libro se hace referencia. ¿Cómo puedo encontrar la fórmula errante? No observo ningún problema en la visualización de información en mis informes.

Encontrar referencias no válidas puede resultar frustrante. Hay varios lugares en los que puede empezar a buscar. El primero está en las fórmulas que están en las hojas de trabajo. (Sí, debe seguir estos pasos para cada hoja de trabajo del libro). Use el cuadro de diálogo Ir a especial (presione F5 y elija Especial) para elegir ir solo a las celdas que contienen errores. Luego puede usar la tecla Tab para moverse entre las celdas que seleccione Excel.

También puede utilizar la herramienta Buscar para buscar posibles errores. Simplemente presione Ctrl + F para mostrar la pestaña Buscar del cuadro de diálogo Buscar y reemplazar, luego busque el carácter #. Asegúrese de decirle a Excel que haga su búsqueda dentro de Fórmulas. Inspeccione todo lo que encuentre para ver si es un error o no.

También debe echar un vistazo a los rangos con nombre definidos en su libro de trabajo. 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 incluya ninguna indicación de error.

Estos no son todos los lugares en los que podría haber errores; Excel es realmente bueno para permitir que existan errores en muchos lugares. Si necesita buscar errores con frecuencia, puede probar una macro que busque en sus fórmulas cualquier error potencial.

Sub CheckReferences()

' Check for possible missing or erroneous links in ' formulas and list possible errors in a summary sheet

Dim iSh As Integer   Dim sShName As String   Dim sht As Worksheet   Dim c As Cell   Dim sChar As String   Dim rng As Range   Dim i As Integer, j As Integer   Dim wks As Worksheet   Dim sChr As String, addr As String   Dim sFormula As String, scVal As String   Dim lNewRow As Long   Dim vHeaders

vHeaders = Array("Sheet Name", "Cell", "Cell Value", "Formula")

'check if 'Summary' worksheet is in workbook   'and if so, delete it   With Application     .ScreenUpdating = False     .DisplayAlerts = False     .Calculation = xlCalculationManual   End With

For i = 1 To Worksheets.Count     If Worksheets(i).Name = "Summary" Then       Worksheets(i).Delete     End If   Next i

iSh = Worksheets.Count

'create a new summary sheet     Sheets.Add After:=Sheets(iSh)

Sheets(Sheets.Count).Name = "Summary"

With Sheets("Summary")

Range("A1:D1") = vHeaders   End With   lNewRow = 2

' this will not work if the sheet is protected,   ' assume that sheet should not be changed; so ignore it   On Error Resume Next

For i = 1 To iSh     sShName = Worksheets(i).Name     Application.Goto Sheets(sShName).Cells(1, 1)

Set rng = Cells.SpecialCells(xlCellTypeFormulas, 23)



For Each c In rng       addr = c.Address       sFormula = c.Formula       scVal = c.Text

For j = 1 To Len(c.Formula)

sChr = Mid(c.Formula, j, 1)



If sChr = "[" Or sChr = "!" Or _           IsError(c) Then           'write values to summary sheet           With Sheets("Summary")

.Cells(lNewRow, 1) = sShName             .Cells(lNewRow, 2) = addr             .Cells(lNewRow, 3) = scVal             .Cells(lNewRow, 4) = "'" & sFormula           End With           lNewRow = lNewRow + 1           Exit For         End If       Next j     Next c   Next i

' housekeeping   With Application     .ScreenUpdating = True     .DisplayAlerts = True     .Calculation = xlCalculationAutomatic   End With

Set wks = Nothing   Set sht = Nothing   Set rng = Nothing

' tidy up   Sheets("Summary").Select   Columns("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 enumerar información sobre los errores detectados en los enlaces de la hoja de trabajo.

También puede utilizar el programa FindLink de Excel MVP Bill Manville, que hace un trabajo increíble al localizar información en enlaces. Puede usar el complemento para buscar el carácter # en todos sus enlaces, lo que debería ayudarlo a localizar los errores. Puede encontrar más información sobre FindLink aquí:

http://www.manville.org.uk/software/findlink.htm

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (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 posterior) aquí:

link: / excelribbon-Tracking_Down_Invalid_References [Rastreo de referencias no válidas].