QUESTION :
Assume, I have Worksheets AZ
.
I want to SUM cell, C21
, across several worksheets (e.g. AZ).
I’ve tried this formula:
=SUM('A:Z'!C21)
However, now if I add rows in sheets AZ, that moves the values of C21
to C24
the formula stays the same, i.e. the formula stays SUM('A:Z'!C21)
, whereas it should now be SUM('A:Z'!C24)
.
I believe I can solve this with an indirect cell reference. Something like:
=SUM('A:Z)'!(Indirect(CEll("address,C21))))
However, this returns an error (“There is a problem with this formula. Not trying to type a formula?….”).
Does anybody know why? And is the right approach or is there a better approach?
ANSWER :
This simple Formula can solve your issue:
=SUM(B:Z!C21)
Or, if you want to use INDIRECT with SUM
then do the following:

Anywhere in Sheet, enter Sheet name and
put one heading like in my sample data
below is, SheetName. 
Select Entire Range including Header, and
from Formula Tab, click Create from
selection and select Top Row option,
finish with Ok.

Enter this formula in any blank Cell
=SUMPRODUCT(SUM(INDIRECT("'"&SheetName&"'!"&"C21:C21")))
Adjust Sheet Name, Cell references and Range Name in Formula as needed.