Variable Sheet/Workbook Reference

marklancer

New Member
Joined
Oct 7, 2002
Messages
1
I'll try my best to explain the situation and hopfully someone will have an answer.

I want to retrieve the value of a cell from another sheet and/or workbook based on the cell value specific cell.

For example in the function:
=[test.xls]Sheet1!A1

I would like to define either [test.xls] or Sheet1 using the value of a cell within the current workbook.

Thanks in advance.
-Mark
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
If A2 contains your file name and A4 contains your sheet number, use the following

=INDIRECT("["&$A$2&".xls]"&"Sheet"&$A$4&"!C3")

This will return the value in [Test]Sheet2!C3

See Example
Test.xls
ABCD
1File Name
2Test5555
3Sheet Num
42
...


HTH
 

Forum statistics

Threads
1,144,210
Messages
5,723,050
Members
422,476
Latest member
beck85

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