Joel bemerkte, dass er beim Schließen einer Arbeitsmappe mit Tausenden von Formeln in letzter Zeit die folgende Meldung erhält: „Eine Formel in diesem Arbeitsblatt enthält eine oder mehrere ungültige Verweise.“ Joel fragt sich, wie er wissen kann, auf welches der sieben Arbeitsblätter in dieser Arbeitsmappe verwiesen wird. Wie finde ich die fehlerhafte Formel? Ich sehe keine Probleme bei der Anzeige von Informationen in meinen Berichten.

Das Aufspüren ungültiger Referenzen kann frustrierend sein. Es gibt mehrere Orte, an denen Sie nachsehen können. Der erste ist in den Formeln, die auf den Arbeitsblättern stehen. (Ja, Sie müssen diese Schritte für jedes Arbeitsblatt in der Arbeitsmappe ausführen.) Verwenden Sie das Dialogfeld Gehe zu Spezial (drücken Sie F5 und wählen Sie Spezial), um nur zu den Zellen zu wechseln, die Fehler enthalten. Sie können dann die Tabulatortaste verwenden, um zwischen den von Excel ausgewählten Zellen zu wechseln.

Sie können auch das Suchwerkzeug verwenden, um nach möglichen Fehlern zu suchen. Drücken Sie einfach Strg + F, um die Registerkarte Suchen des Dialogfelds Suchen und Ersetzen anzuzeigen, und suchen Sie dann nach dem Zeichen #. Stellen Sie sicher, dass Sie Excel anweisen, die Suche in Formeln durchzuführen. Überprüfen Sie alles, was gefunden wird, um festzustellen, ob es sich um einen Fehler handelt oder nicht.

Sie sollten sich auch alle in Ihrer Arbeitsmappe definierten benannten Bereiche ansehen. Sehen Sie sich jeden Namen im Dialogfeld Definieren (Einfügen | Name | Definieren) an und stellen Sie sicher, dass alles, was sich im Feld Verweise auf befindet, keine Fehleranzeigen enthält.

Dies sind nicht alle Stellen, an denen Fehler auftreten können. Excel ist wirklich gut darin, Fehler an vielen Stellen auftreten zu lassen. Wenn Sie häufig nach Fehlern suchen müssen, können Sie ein Makro ausprobieren, das Ihre Formeln nach möglichen Fehlern durchsucht.

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

Dieses Makro erstellt ein Arbeitsblatt mit dem Namen „Zusammenfassung“, in dem Informationen zu Fehlern aufgelistet werden, die in den Arbeitsblattlinks festgestellt wurden.

Sie können auch das FindLink-Programm von Excel MVP Bill Manville verwenden, mit dem sich Informationen in Links hervorragend finden lassen. Sie können das Add-In verwenden, um in all Ihren Links nach dem Zeichen # zu suchen, das Ihnen beim Auffinden der Fehler helfen soll. Weitere Informationen zu FindLink finden Sie hier:

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

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (8662) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Aufspüren ungültiger Referenzen.