Linking to Sheets

[racer25]

Guys,

I am trying to create a consolidated sheet of various other Excel
sheets

Sheet 1 is called Consol
Sheet 2 is called Player 2
Sheet 3 is called Player 3

On Consol I want to pull that data from sheet 1 and sheet 2 into column
B and Column C, Column A is going to have field names.

At the top of column B I want to reference the sheet name so it prints
"Player2" and for all the linking formula rather than ='Player 1'!A1 I
would like a formula that looks at B1 for the sheet name rather
manually adding it into the formula something along the lines of
=B1toA1 B1 being the sheet name of where to look at A1.

Hopefully this makes sense and I thank you in advance for reading and
hopefully offering your advice.

 

[Max]

One way is to use INDIRECT*

In Consol
--------
With B1 across containing the sheetnames*: Player2, Player3, ..
Put in B2: =INDIRECT("'"&B$1&"'!A"&ROW(A1))
Copy B2 across & fill down

The above returns equivalently in B2 and C2 down:
=Player2!A1, =Player3!A1
=Player2!A2, =Player3!A2
etc

*Note that the sheetnames listed must match the actual names in the sheet
tabs. I've assumed the tabs are named: Player2, Player3, .. (without the
spaces)