- Referring to closed workbook with variable file path
Currently I only found I could do this by INDIRECT, because INDEX could link to a closed workbook but seems not able to have variable file path referring to worksheet values.
Example:
3 Workbooks in total, which are Workbook A, Workbook B and Workbook C
Workbook A: Formula driven, refer to Workbook B/C data
Workbook B: Value only data, which is Week 1 data below
Workbook C: Value only data, which is Week 2 data below
Workbook A:
- Workbook A is to show result following to drop down list (B2) selection (Week), means when I choose different weeks, suppose formula's file path will change accordingly
1.jpg
Workbook B:
3.jpg
- Source data
Workbook C:
4.jpg
- Source data
Source data will be placed accordingly in correct path, so I know the pattern of file path, but problem is only INDIRECT can only work with opened workbooks, or it will show "#REF" with closed workbooks.
Alternatives I thought of:
1. using INDEX instead -> but since the file path is having variables (Week 1 to Week 2), I think INDEX doesn't work
2. using VBA -> since the path s having variables, I am not familiar with the coding...
3. Find & Replace all formula cells (i.e. Replace all "Week 1" with "Week 2") every time -> it works but since the replace range is including "cross file", excel pop up update value alerts per line, so it is kind of annoying and inconvenient
4. fix the path and file name, i.e. I manual rename those data and put them all in a fixed path, then everytime I open "Workbook A
, I save as a weeklyreport -> it works too, but very inconvenient too..
Sorry for my bad english and please see if you guys could help. Much appreciated.
Bookmarks