Change Data Source in Pivot Table

[stags81]

Hello,

I'm trying to find a seamless way to change the data source file in a pivot table without manually going in and editing the text via Pivot Table Tools -> Options -> Change Data Source.

Ideally, I would somehow build a reference using a HYPERLINK-CONCATENATE formula that I build, either in the workbook or using a named range. From there, I would use that formula or named range in the Change Data Source function.

The data set files I’m pulling into the pivot table have similar paths (C:/Drive/Folder/20180911, C:/Drive/Folder/20180910), so if I did a HYPERLINK-CONCATENATE formula I’d ideally change one part of the field (e.g. 20180911 to 20180910). But, the lengths of the files will differ so I’d have to do an OFFSET-COUNTA formula to account for that.

I have quite a few files to go through and analyze via the pivot table, so I’m looking to understand the following:

• Is it possible to use a formula in Pivot Table Tools -> Options -> Change Data Source that is built via the HYPERLINK function? I’m using =HYPERLINK(Cell reference) in the file with the pivot table but I’m getting the “Reference is not valid” error.
• Is it possible to refer to a formula in Name Manager that I could use in Change Data Source? I’m using =HYPERLINK(Cell reference) in Name Manager but I’m getting the “Reference is not valid” error when entering that name in Change Data Source.
• Is there another way to change the data source to a file that will need to change frequently and where the range will be similar each time?

Thanks!

Mike