How to find "#VALUE" cells in Excel?

In Excel 2007 I have two sheets:

• sheets1 with data
• sheets2 links with some formulas data from sheets1

If a formula does not work in sheets2 I have cell that shows #VALUE!, but in the bar I can see the formula. I would like to check if in the sheets2 there are any cell with #VALUE to be corrected.

It is not possible to use FIND function to search for "#VALUE", because in the bar there are no those chars.

1 Answer

Set up the following option:

Look in: Values

If you want to count #VALUE! errors in Excel 2007, you may use the following array formula (entered using Ctrl+Shift+Enter instead of regular Enter):

=SUM(IFERROR(--(ERROR.TYPE(sheets1!$B$1:$B$10)=3),0))

^{Note that array formulas always do calculations for every cell in the reference, so providing the whole column will not be efficient.}