Joel ha notato che quando chiude una cartella di lavoro che contiene migliaia di formule, ultimamente riceve questo messaggio: “Una formula in questo foglio di lavoro contiene uno o più riferimenti non validi”. Joel si chiede come possa sapere a quale dei sette fogli di lavoro in questa cartella di lavoro si fa riferimento. Come posso trovare la formula errante? Non osservo alcun problema nella visualizzazione delle informazioni sui miei rapporti.

Trovare riferimenti non validi può essere frustrante. Ci sono molti posti in cui puoi iniziare a cercare. Il primo è nelle formule che si trovano sui fogli di lavoro. (Sì, è necessario eseguire questi passaggi per ogni foglio di lavoro nella cartella di lavoro.) Utilizzare la finestra di dialogo Vai a speciale (premere F5 e scegliere Speciale) per scegliere di andare solo alle celle che contengono errori. È quindi possibile utilizzare il tasto Tab per spostarsi tra le celle selezionate da Excel.

Puoi anche utilizzare lo strumento Trova per cercare possibili errori. Basta premere Ctrl + F per visualizzare la scheda Trova della finestra di dialogo Trova e sostituisci, quindi cercare il carattere #. Assicurati di dire a Excel di eseguire la ricerca all’interno delle formule. Ispeziona tutto ciò che viene trovato per vedere se si tratta di un errore o meno.

Dovresti anche dare un’occhiata a tutti gli intervalli denominati definiti nella tua cartella di lavoro. Guarda ogni nome nella finestra di dialogo Gestione nomi (scheda Formule, fai clic sullo strumento Gestione nomi), assicurandoti che tutto ciò che è nella colonna Si riferisce a non includa alcuna indicazione di errore.

Questi non sono tutti i punti in cui potrebbero esserci errori; Excel è davvero bravo a lasciare che gli errori esistano in molti posti. Se devi cercare spesso gli errori, potresti provare una macro che esamina le tue formule per eventuali errori.

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, 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

' tidy up   Sheets("Summary").Select   Columns("A:D").EntireColumn.AutoFit   Range("A1:D1").Font.Bold = True   Range("A2").Select End Sub

Questa macro crea un foglio di lavoro denominato “Riepilogo” utilizzato per elencare le informazioni su eventuali errori rilevati nei collegamenti del foglio di lavoro.

Puoi anche utilizzare il programma FindLink di Bill Manville, MVP di Excel, che fa un ottimo lavoro nell’individuare le informazioni nei collegamenti. È possibile utilizzare il componente aggiuntivo per cercare il carattere # in tutti i collegamenti, il che dovrebbe aiutarti a individuare gli errori. Non è chiaro se il componente aggiuntivo funzionerà con Excel 2013, poiché l’ultimo aggiornamento per il file (al momento della stesura di questo documento) è stato nel 2011. Tuttavia, vale la pena provare; maggiori informazioni su FindLink possono essere trovate qui:

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

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (8664) si applica a Microsoft Excel 2007, 2010 e 2013. Puoi trovare una versione di questo suggerimento per la vecchia interfaccia del menu di Excel qui:

Tracking Down Nonvalid References.