#REF! errors from summing across multiple tabs

Question

I have 70 tabs (Tab1,Tab2,Tab3,...,Tab70) and I have a formula in tab 1, Cell A25 to sum up from Cell A25: =SUM(Tab1:Tab70!A25).

My issue is the formula returns a #REF! error and I would like to know is there a way for me to identify which tab is returning the #REF! error?

All tabs have been standardized in their formats, cell content and formula .

Previously, the formula was such that in Cell A25 of tab 1 = Tab2!A25+Tab3!A25+Tab4!A25+Tab5!A25+...+Tab69!A25+Tab70!A25.

So, I can easily breakdown the formula and identify which tab is returning the REF error, but since changing the formula to (Tab1:Tab70!A25), I find I cannot break it down as before when it comes to troubleshooting.

Recap:

I have a formula which sums 1 particular cell across multiple tabs (all formatted the same and similarly formularized) and is returning a #REF! error.

The sum formula SUM(Tab1:Tab70!A25) doesn't allow me to breakdown the formula to see which tab is returning the error - is there a way for to identify which tab is giving me the error other than going through each tab 1 by 1?

Answer 1

You can use indirect formula to get all the tab A25 value and you should add a help column for sheet name.

=INDIRECT("'"&B1&"'!A25")

Column B is the sheet Name

To get name of all the sheet.

1. Formula > NameRange>New

Name: "GetTabName"

Refer to

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

2.=TOCOL(GetTabName)

It could be someone accidently deleted the cell A25 in one of your sheet. You should choose your 3D-reference again.

Best Regards,

Snow Lu

Answer 2

You are welcome. Have a nice day:)

Answer 3

Hi Snow,

Thank you for the solution !

I forgot that i had hidden tabs and some of these tabs are the ones with the #REF errors.

Having the formula to list all the tabs helped to identify which were the erroneous tabs.

Thank you!

Answer 4

Hi,

In range A2;A4, type all the worksheet name. In cell B2, enter this formula

=BYROW(A2:A4,LAMBDA(r,INDIRECT("'"&r&"'!A25")))

Hope this helps.