Pivot Tables - Table/Range based off formula

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
724
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2010
  5. 2007
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,212,947
Messages
6,110,837
Members
448,302
Latest member
sniffit1st

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top