Pivot Tables - Table/Range based off formula

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
287
Office Version
  1. 2016
  2. 2010
Platform
  1. 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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,529
Members
410,845
Latest member
OldSwimmer1650
Top