I've been racking my brain on this for a while and can't seem to make it work (i'm not sure if it's possible). In simple terms, I would like a pivot table to refresh with the data source based on a specific cell which would be a dynamic named range.
I pull data daily and name each worksheet based on the days date. i.e. for today, I would name the worksheet: "4.21"
I have a "inputs" worksheet which includes a list of every range of every tab in column B:
"4.20'!A1:L120"
"4.21'!A1:L110"
"4.22'!A1:L112"
In the "inputs" worksheet, there is a specified cell that returns the last named range in column B. This cell is named "LatestDate" as a named range.
I have a pivot table and would like to replace the "data source" using this "LatestDate" value. I tried using: indirect(LatestDate), however it returns an error saying I need at least 2 rows of data. The current data source for the pivot table is '4.22'!A1:L112, however, I can't seem to replace it with a dynamic formula incorporating the "LatestDate" named range...i'm not sure if this is possible?
Appreciate anyone looking into this for me...
Bookmarks