Forum Discussion
How to control a workbook reference (file path) with a cell
So I have a master workbook that has multiple sheets referenced to multiple work books. I want to modified the file paths in the workbook references with cell input on the master workbook. So an example is:
Masterwork book- June numbers (with sheets for John,Cindy, and Sue( each sheet is referenced to the sales report for each individual)
The reference for each individual work book is ='https://company.sharepoint.com/site/sales/2022/Feb/[(individuals name).xlsx]Sheet1'$B$4
What I want to control is the 2022 and Feb file path with cells on the master, so if enter 2021 in A2 and Mar on B2 in the master workbook on sheet 2, I want the reference to change to ='https://company.sharepoint.com/site/sales/2021/Mar/[(individuals name).xlsx]Sheet1'$B$4
Any ideas? I have researched and found the =INDIRECT() function but am not sure if it will work in this instance. Thanks ahead of time for any and all help. I also am not fimilar with using macros or VBA.
Masterwork book- June numbers (with sheets for John,Cindy, and Sue( each sheet is referenced to the sales report for each individual)
The reference for each individual work book is ='https://company.sharepoint.com/site/sales/2022/Feb/[(individuals name).xlsx]Sheet1'$B$4
What I want to control is the 2022 and Feb file path with cells on the master, so if enter 2021 in A2 and Mar on B2 in the master workbook on sheet 2, I want the reference to change to ='https://company.sharepoint.com/site/sales/2021/Mar/[(individuals name).xlsx]Sheet1'$B$4
Any ideas? I have researched and found the =INDIRECT() function but am not sure if it will work in this instance. Thanks ahead of time for any and all help. I also am not fimilar with using macros or VBA.
2 Replies
- yes true indirect will work in your case
- Even while having the referenced workbooks closed? Also where would I put the function? I tried placing it at the front and In the locations that I want the file path variable to be without anything other than !ref. Even with the other workbook open.
