Use Cell Contents to Select Input Sheet in Formula??

jfuredy

Board Regular
Joined
Mar 11, 2003
Messages
72
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
Code:
RANGE FORMULA (1st cell)
B2:D4 =INDIRECT($A2&"!"&B$1)

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik
 

jfuredy

Board Regular
Joined
Mar 11, 2003
Messages
72
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
 

Forum statistics

Threads
1,136,427
Messages
5,675,784
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top