Reading excel file dynamically

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi
I am preparing a master excel sheet say it is "master-1" work book. When I enter the data sheet (another excel work book but in that sheet name is always same) name in a cell, the other cell in the master excel should read the data file and populate the certain values.

I used the following command. It worked well when the data file name is hard coded.
='[data file name]work book-1'!B3
Here data file name will change but work book-1 will not change

But my case, I want to read the data file dynamically based on the file name entered in the cell(say input cell is A1 in the master excel)

Your help in this regard is greatly appreciated.

Thanks,
Vijay

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If the other workbook is open you can use

=INDIRECT(ADDRESS(3,2,1,1,A1 & "master-1"))

In the above example A1 contained C:\Users\haadi\Downloads\[Xl00000001.xls]

In case the workbook is close, then download an addon to Excel called MOREFUNC from

https://download.cnet.com/Morefunc/3000-2077_4-10423159.html

Then you can use this formula

=INDIRECT.EXT(ADDRESS(3,2,1,1,A1 & "master-1"))

Again in this case A1 contains the workbook name as for example C:\Users\haadi\Downloads\[Xl00000001.xls]