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 fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,114,524
Messages
5,548,553
Members
410,848
Latest member
anuradhagrewal
Top