cell value as a worksheet reference

[Stepney_Clint]

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...

[Peo Sjoblom]

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...

[Stepney_Clint]

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...