Use Cell Contents to Select Input Sheet in Formula??

[jfuredy]

I am setting up a workshee that is kind of a summary sheet for a workbook with many sheets. I would like a formula that will look at the contents of cell A1 (e.g. 3) and use that number to pull various info from a worksheet titled "3" and return those values to B1, C1, etc.

For example, I could enter the worksheet numbers in the first column of the summary sheet and have it automatically return info from that sheet such as the Title and some numerical results from that sheet. Then I will repeat these formulas for multiple rows in the summary sheet in order to ensure that the titles match the numbers and the worksheet names.

I would have thought that this would be easy, but I have not found a formula to do this in Excel 2000 yet. Any advice would be greatly appreciated.
Joe

 

[erik.van.geit]

Hi, Joe,

seems like you could use INDIRECT

  A      B      C      D      
2 sheet1 sh1 A1 sh1 B1 sh1 C1 
3 sheet2 sh2 A1 sh2 B1 sh2 C1 
4 sheet3 sh3 A1 sh3 B1 sh3 C1 

summary
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B2:B4 =INDIRECT($A2&"!A1")
C2:C4 =INDIRECT($A2&"!B1")
D2:D4 =INDIRECT($A2&"!C1")

[Table-It] version 06 by Erik Van Geit

OR
putting cellreferences on top

  A      B      C      D      
1        A1     B1     C1     
2 sheet1 sh1 A1 sh1 B1 sh1 C1 
3 sheet2 sh2 A1 sh2 B1 sh2 C1 
4 sheet3 sh3 A1 sh3 B1 sh3 C1 

summary
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B2:D4 =INDIRECT($A2&"!"&B$1)

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[jfuredy]

THank You, Erik!

Erik,

Thanks, INDIRECT() was exactly the function that I needed, I just didn't know it existed. Thanks for making the solution to my problem so simple!

Joe