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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0

Forum statistics

Threads
1,218,496
Messages
6,142,817
Members
450,449
Latest member
Dave Carr QM

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
Back
Top