cell value as a worksheet reference
 

I am trying to create a worksheet that consolidates data from a set of other
worksheets. The other sheets are all formatted identically. What I am having
difficulty with is using the name of the other tabs in my formulas in the
summary tab. I have the name of the tab in the a cell but can't figure out
how to use that cell value as a worksheet reference. For example, in the
summary tab, A2="Source1" but I get an invalid formula error when I enter
this into B2: =(a2)!b17. Any help would be appreciated...

You would need to use INDIRECT

=INDIRECT("'"&A2&"'!B17")


Regards,

Peo Sjoblom

"Stepney_Clint" wrote:

I am trying to create a worksheet that consolidates data from a set of other
worksheets. The other sheets are all formatted identically. What I am having
difficulty with is using the name of the other tabs in my formulas in the
summary tab. I have the name of the tab in the a cell but can't figure out
how to use that cell value as a worksheet reference. For example, in the
summary tab, A2="Source1" but I get an invalid formula error when I enter
this into B2: =(a2)!b17. Any help would be appreciated...

thanks...

"Peo Sjoblom" wrote:

You would need to use INDIRECT

=INDIRECT("'"&A2&"'!B17")


Regards,

Peo Sjoblom

"Stepney_Clint" wrote:

I am trying to create a worksheet that consolidates data from a set of other
worksheets. The other sheets are all formatted identically. What I am having
difficulty with is using the name of the other tabs in my formulas in the
summary tab. I have the name of the tab in the a cell but can't figure out
how to use that cell value as a worksheet reference. For example, in the
summary tab, A2="Source1" but I get an invalid formula error when I enter
this into B2: =(a2)!b17. Any help would be appreciated...