Can I create a formula where a tab name is in a cell reference?

[sitewolf]

I'm not sure I'm saying this coherently.
I'm creating a spread with tabs filled with data I'm creating stats from- by and large simple counts from various columns...easy peasy.
But I'd like to create a cumulative data sheet where I use tab names as a header, in other words lining each sheet's data side by side with every other sheet.

How do I use those headers in my formula to generate stats from each sheet?
i.e. instead of referencing a tab called Data with a formula looking at Data!A1, how do a pull in that A1 data with Data as the column header?

 

[jasonb75]

You can do that with the INDIRECT function. Assuming header in B1,

=INDIRECT("'"&B$1&"'!A1")

Note that A1 will be absolute even though there are no $ signs. To make it relative you would either need to use R1C1 references or a more complex formula.

Also, have a read of the page linked below before using INDIRECT. There are downsides to it which are explained there.

Volatile Excel Functions -Decision Models

Microsoft Excel Calculation Secrets and optimisation tips, calculation methods, calculation sequence, dependencies and memory limits.

www.decisionmodels.com

 

[Misca]

The INDIRECT function turns cell values into references:

=INDIRECT("'"&A1&"'!B5")

The example takes the sheet name from A1 and shows the value of cell B5 on that sheet.

 

[sitewolf]

I greatly appreciate the answers from you both!