Joel a noté que lorsqu’il ferme un classeur qui contient des milliers de formules, il reçoit ce message récemment: « Une formule dans cette feuille de calcul contient une ou plusieurs références non valides. » Joel se demande comment il peut savoir à laquelle des sept feuilles de travail de ce manuel fait référence. Comment puis-je trouver la formule errante? Je n’observe aucun problème dans l’affichage des informations sur mes rapports.

La recherche de références invalides peut être frustrante. Il y a plusieurs endroits où vous pouvez commencer à regarder. Le premier est dans les formules qui sont sur les feuilles de calcul. (Oui, vous devez effectuer ces étapes pour chaque feuille de calcul dans le classeur.) Utilisez la boîte de dialogue Atteindre spécial (appuyez sur F5 et choisissez Spécial) pour choisir d’accéder uniquement aux cellules contenant des erreurs. Vous pouvez ensuite utiliser la touche Tab pour vous déplacer parmi les cellules sélectionnées par Excel.

Vous pouvez également utiliser l’outil Rechercher pour rechercher d’éventuelles erreurs. Appuyez simplement sur Ctrl + F pour afficher l’onglet Rechercher de la boîte de dialogue Rechercher et remplacer, puis recherchez le caractère #. Assurez-vous de dire à Excel de faire sa recherche dans les formules. Inspectez tout ce qui est trouvé pour voir s’il s’agit d’une erreur ou non.

Vous devez également examiner toutes les plages nommées définies dans votre classeur. Examinez chaque nom dans la boîte de dialogue Gestionnaire de noms (onglet Formules, cliquez sur l’outil Gestionnaire de noms), en vous assurant que tout ce qui se trouve dans la colonne Fait référence à n’inclut aucune indication d’erreur.

Ce ne sont pas tous les endroits où il pourrait y avoir des erreurs; Excel est vraiment bon pour laisser des erreurs exister dans de nombreux endroits. Si vous avez souvent besoin de rechercher des erreurs, vous pouvez essayer une macro qui examine vos formules pour détecter d’éventuelles erreurs.

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

Cette macro crée une feuille de calcul appelée «Résumé» qui est utilisée pour répertorier les informations sur les erreurs détectées dans les liens de feuille de calcul.

Vous pouvez également utiliser le programme FindLink d’Excel MVP Bill Manville, qui fait un travail incroyable de localisation d’informations dans les liens. Vous pouvez utiliser le complément pour rechercher le caractère # dans tous vos liens, ce qui devrait vous aider à localiser les erreurs. Il n’est pas clair si le complément fonctionnera avec Excel 2013, car la dernière mise à jour du fichier (au moment de la rédaction de cet article) remonte à 2011. Cela vaut la peine d’essayer; plus d’informations sur FindLink peuvent être trouvées ici:

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

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (8664) s’applique à Microsoft Excel 2007, 2010 et 2013. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Tracking_Down_Invalid_References [Suivi des références invalides].