Thread: HOW-??accumulating values from same cell in multiple worksheets

I am trying to automatically change the name of a worksheet in a formula

=sum(pay1:pay26!a1)

I have to manually chang the pay number each pay period from 1 to 2 to 3 etc

I can create a string in a cell with the right name but cant figure out how to replace the name in the formula

EG pay2

Anybody have any ideas-even if I can reference the sheet name tab it owuld work

Originally Posted by aquarian1000

I am trying to automatically change the name of a worksheet in a formula

=sum(pay1:pay26!a1)

I have to manually chang the pay number each pay period from 1 to 2 to 3 etc

I can create a string in a cell with the right name but cant figure out how to replace the name in the formula

EG pay2

Anybody have any ideas-even if I can reference the sheet name tab it owuld work

Hi,

Checkout the INDIRECT() function, which will allow you use the name string in the cell, in the formula. Or alternatively use the following to calculate the sheet name in a cell, and build this cell into the INDIRECT() function

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

HTH

which will return the