INDIRECT and Non contiguous ranges

[helpme290]

This is my first post on MREXCEL and am excited to start learning!

I am trying to SUM a Dynamic INDIRECT formula the only Issue the I am trying to sum cells AZ28, BA26, W26 and X26

 

[slinky]

Where does your INDIRECT formula sit?

Theoretically speaking, =sum(AZ28, BA26, W26, X26) should work, unless, of course, the output of the formulas populating those cells isn't delivering numeric values.

 

[helpme290]

Sorry should have explained better!

So I need to collect data from about 200 different sheets. The sheets all have different names. I need to sum the Data within cells AZ28, BA26, W26 and X26 in each one of those sheets.

INDIRECT("'" & $E19 & "'!" & P$2) - this is the formula i am using when i need to just get the data for cell AZ28.

Cell E19 is where the sheet name is and N2 is where i placed AZ28

Am I being confusing?

 

[helpme290]

Sorry cell P2 is where I placed cell AZ28

 

[slinky]

Are the values in the same place on every sheet, and the sheets consecutive?
If so, you can do a 3D formula.

=sum(sheet2:sheet202!AZ28)

Will give you the sum of AZ28 on sheets sheet2->sheet202

 

[helpme290]

Thanks Slinky! However I have them Sum of AZ28 for all of the sheets.


I am trying to sum Cells: AZ26, BA26, W26 and X26 so I can get a total for each particular sheet.

 

[slinky]

So, for an example I chucked a sheet name in F1 and Cell References If G1 - J1..

=SUM(INDIRECT(F1&"!"&G1),INDIRECT(F1&"!"&H1),INDIRECT(F1&"!"&I1),INDIRECT(F1&"!"&J1))

I wouldn't want to use that many times, but it does the job.

 

[helpme290]

That was what I was afraid of I was hoping to find a way around that but I dont think its possiable. Thanks for you help Slinky!

 

[slinky]

I just want to make sure I understand exactly what you're trying to do.

Do you want a list of totals per sheet? i.e.
Sheet 1 - Total
Sheet 2 - Total
Sheet 3 - Total

Or do you want something else?
If it's the above, and you're going to set it up once and use it repeatedly, I'd invest the energy in creating the formulas properly so as to cut down on the long term potential issue with using hundreds of INDIRECT formulas.

 

[helpme290]

Yes the list totals per sheet. I was afraid that using a mile long indirect formula is the only way.

Thanks for your help