How to sum a cell across several worksheets using indirect cell reference

Assume, I have Worksheets A-Z.

I want to SUM cell, C21, across several worksheets (e.g. A-Z).

I’ve tried this formula:

=SUM('A:Z'!C21)

However, now if I add rows in sheets A-Z, 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?

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.