Linking to Sheets

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.

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"racer25" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> racer25
> ------------------------------------------------------------------------
> racer25's Profile:
http://www.excelforum.com/member.php...o&userid=13057
> View this thread: http://www.excelforum.com/showthread...hreadid=481804
>