reading excel file dynamically Closed

Question

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

rizvisa1 · Answer

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]

Reading excel file dynamically

1 response

Similar discussions

Subscribe To Our Newsletter!