Google Sheets: Find Originating Cell Of Cascading Error

Question

I use a lot of calculations in a spreadsheet with ~3M cells. Many of the these calcs are cascading, ie they use the results of previous calcs. I find it a very tedious task to track back through the cell formulas to find the cell where the error originated, especially since I use ArrayFormula() and Index() extensively to populate hundreds/thousands of rows in columns.

The following shows a very simplistic example with made-up formulas for illustration only - rows 5-12 just describe what a cascading sequence of formulas might look like (in reality they are far more complex equations):

I'm looking for a way to display the originating cell in the Error message, so that any subsequent cell would show something like:

Error: Cell D4: Did not find value 'J4-75' in VLOOKUP evaluation.

Has anyone got a suggestion on how this could be done?

player0 player0 · Accepted Answer · 2021-01-10T09:42:08

I guess thats the purpose of these functions:

IFERROR
IFNA
ERROR.TYPE
ISERR
ISERROR
ISNA
ISREF

where you can type your own error messages so instead of this:

you would get this:

or to make it more violent:

Google Sheets: Find Originating Cell Of Cascading Error

1 Answers