ItalianPlatinum
Well-known Member
- Joined
- Mar 23, 2017
- Messages
- 793
- Office Version
- 365
- 2019
- Platform
- Windows
Hello - I have quite a few pivot tables in my workbook. and the path of where the data it references could change, its not housed in the same workbook as the pivot tables. To have it dynamically update i was looking for if there is a way for having the table/range set to a combination of a cell value & a name of the file.
For example this:
Cell A1 (tab called "MAIN") where pivot tables are: 'C:\Program Files\
Pivot table Range now: 'C:\Program Files\[My File.xlsx]My File'!$A:$X
Desired link in the pivot table range range: "MAIN!$A$1" & "[My File.xlsx]My File'!$A:$X"
But that file may change to another folder. So wanted to make it where the user would input the folder where the file are then it dynamically updates across the board. Of course the desired link isnt working, getting an error of "reference is not valid."
Any help is appreciated.
For example this:
Cell A1 (tab called "MAIN") where pivot tables are: 'C:\Program Files\
Pivot table Range now: 'C:\Program Files\[My File.xlsx]My File'!$A:$X
Desired link in the pivot table range range: "MAIN!$A$1" & "[My File.xlsx]My File'!$A:$X"
But that file may change to another folder. So wanted to make it where the user would input the folder where the file are then it dynamically updates across the board. Of course the desired link isnt working, getting an error of "reference is not valid."
Any help is appreciated.
