using indirect to reference sheet name or range name

Posted by Steve on September 19, 2001 8:20 AM

I am trying to reference a sheet name or range name in a formula using indirect. The name of the sheet or range is in cell B7 and changes periodically. I need to be able to reference the sheet or range based on the contents of cell B7. Any help would be greatly appreciated.

Posted by Steve on September 19, 2001 7:27 AM

Thanks

Thanks! The second formula did exactly what I wanted!

Posted by Aladin Akyurek on September 19, 2001 8:28 AM

Steve,

Is this a different question that you posed earlier at

30699.htm ?

Aladin

Posted by Rob Jackson on September 19, 2001 8:29 AM

Use the expression INDIRECT(B7). So, for example...

=sum(INDIRECT(B7))
where B7 containss the string 'Sheetname'!A1:A10 (or whatever)
Could also be...

=sum(INDIRECT("'" & B7 & "'!A1:A10"))

NOTE: The ' are only required in the formula when the sheet name contains spaces. It is an idea to put them in incase.

All yours...