Referencing sheet names in formula

[acarter12078]

Hi.
I have a summary sheet that is bringing in values from 131 different worksheets.
At the moment the few totals I have are referenced manually by typing in the sheet reference in the formula.
=HBH!$G$41 (sheet name HBH and the total is in cell G41)

In my summary page I have the sheet reference listed (in this case its cell C10)

I can't seem to get a sheet reference to work in the formula.
In total I need to bring across 700+ references so don't want to do it manually.

any help would be appreciated.

thanks
adam

 

[FormR]

Hi welcome to the forum!

You could use the INDIRECT() function for that - for example:

=INDIRECT("'"&C10&"'!$G$41")

You should note that this comes with some downsides, not least that it's a volatile function and it's overuse may cause your workbook to bog down.

 

[acarter12078]

Hi welcome to the forum!

You could use the INDIRECT() function for that - for example:

=INDIRECT("'"&C10&"'!$G$41")

You should note that this comes with some downsides, not least that it's a volatile function and it's overuse may cause your workbook to bog down.

thats great thank you. it works great and so dar doesn't seem to have bogged down the sheet.