Hi - Dave here.
One of our readers reached out last week with an interesting problem about how to count errors on all sheets in a workbook. The idea is to provide a simple way to audit the sheets in a workbook for errors.
It is possible to use the IFERROR and INDIRECT functions to do this. You can see the result in the workbook below, where the formula in cell C5 is:
=SUMPRODUCT(--(ISERROR(INDIRECT("'"&B5&"'!A1:F200"))))
[
Download the workbook and read the full explanation]
This problem breaks down into two main parts: (1) List all sheet names and (2) Count errors on each sheet. The second part (counting errors) is explained in the article above, which also includes the formula to create a link to each sheet with the HYPERLINK function. The first part (listing sheet names) is explained in a
separate article here, which covers how to create a list of sheet names with a formula or with Power Query.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great week!
Dave
