For anyone else that’s spent hours combing their file, the problem can also exist if you’ve copied a data validation range over from another workbook.
To fix it :
Ribbon–>File–>Check for Issues–>Check Compatibility
This will bring up the checker that will tell you if Validation points to an external sheet.
Most importantly, it will tell you which sheet it is on.
Anyway once you know the sheet(s), go to the sheet. Now
Ribbon–>Home–>Down arrow next to Find and Select–>Data Validation.
This will select the cells that have Data Validation applied.
and fix the broken reference in the “Source” field, or click “Clear All” if you don’t need it.
In the end I tracked this down to the conditional formatting rules.
Clicking on “Home – Conditional Formatting – Manage Rules” brings up the following dialog, which is relatively easy to look through and replace the external references.
If the workbook is large is not easy find the format condition with external reference.
I write this VBA function for find it.
Limited to 80 columns and 500 row for reduce execution time.
when function stop you can check the position asking:
?foglio.name ?cella.row ?cella.column Public Function CercaLink() Dim Cella As Object, i&, Foglio As Object For Each Foglio In ActiveWorkbook.Sheets ActiveWorkbook.Sheets(Foglio.Name).Select For Each Cella In ActiveSheet.Cells If Cella.Column < 80 Then If Cella.FormatConditions.Count > 0 Then For i = 1 To Cella.FormatConditions.Count If InStr(1, Cella.FormatConditions(i).Formula1, ":\") > 0 Then Stop Next End If End If If Cella.Row > 500 Then Exit For Next Next End Function