Hi,
I have different workbooks with data.
eg workbook "data_red". workbook "data_blue" and workbook "data_green". The sheetnames are the same in the different workbooks.
Now I have a workbook called "Overview". In this workbook I have a formula in cells A10..A110 that is linked to data in workbook "data_blue" (=[Data_blue.xlsx]Sheet1!$A$1 and so on.
If I want to use the data from the other workbook, eg "data_green.xlsx", I have to change al the formulas in the cells of workbook " Overview".
What I like to do is to put a variable in the formula =[Data_blue.xlsx]Sheet1!$A$1 on the position of the workbook [Data_blue.xlsx]. So that I have a cell B1 in the Overview workbook where I put the name of one of the other workbooks, eg workbook "data_blue". And that the formula now is changed to get the data from the other workbook.
Hopefully do you understand what I mean. And do you have a solution for it
The INDIRECT function allows for that, but unfortunately, it only works with open workbooks.
e.g.
=INDIRECT('"["&$B$1&".xlsx]Sheet1'!$A$1")
If you can download and install a free addin called Morefunc
then you can use the INDIRECT.EXT to work with closed workbooks
=INDIRECT.EXT('"["&$B$1&".xlsx]Sheet1'!$A$1")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks