Change Pivot Data Source Based on Cell Selection

[elbarauljr]

Hello,

Workbook name: Report
Sheet with pivot: Summary
Sheets with data source: Source1 , Source2 (data in range A1:C14)

In the above workbook, I defined the name PivotChange_Source with formula =INDIRECT(Summary!$A$1&"!A1:C14") to change the Pivot source within the Workbook based on A1 input to select the correct source and then refresh the pivot. All working ok, attached picture for this situation.

The problem is that I need to change the Pivot source coming from other workbook named "Source" that is in directory U:\ , this workbook have the same source sheets Source1 , Source2 and same source range A1:C14
I tried input in cell A1 U:\[Source.xlsx]Source1, but refreshing the pivot is shows error "Reference isn't valid"

What would be the correct input in cell A1 or formula change in the Name Manager to properly refresh the pivot coming from the workbook named "Source" ?

Thanks in advance.

 

[awoohaw]

are you using your 2007 version or the 365 version of excel?
if you are using the 2007 version, you may need to enter that formula with Cntl-Shft-Enter, as per this MS article:

Create workbook links - Microsoft Support

You can refer to the contents of cells in another workbook by creating an external reference formula. An external reference (also called a link) is a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook.

support.microsoft.com

if you are using 365, then the above article is moot.

 

[elbarauljr]

Using 365, tried to modify the formula per the article but still getting the "Reference isn't valid" when refreshing the pivot table. Attached picture is showing the issue.

Not sure if the issue is the formula in the Name Manager or the dropdown source list U:\[Source.xlsx]Source1

Pivot refresh is only working when selecting source that are within the same workbook, but not from the external workbook. Any idea what is not working ?

 

[awoohaw]

Using 365, tried to modify the formula per the article but still getting the "Reference isn't valid" when refreshing the pivot table. Attached picture is showing the issue.

Not sure if the issue is the formula in the Name Manager or the dropdown source list U:\[Source.xlsx]Source1

Pivot refresh is only working when selecting source that are within the same workbook, but not from the external workbook. Any idea what is not working ?

when you look at Name Manager do you see the U:\[Source.xlsx]Source1 cell reference address? this is getting beyond my expertise.

 

[elbarauljr]

here is a screenshot of the Name Manager. the Pivot refresh when selecting a source within the workbook, but not when selecting the source U:\[Source.xlsx]Source1

 

[awoohaw]

what happens when you only have this value in cell in your name manager formula?

=INDIRECT(Summary!$A$1)

 

[elbarauljr]

what happens when you only have this value in cell in your name manager formula?

=INDIRECT(Summary!$A$1)

The formula =INDIRECT(Summary!$A$1) in the name manager only works for sources within the same workbook "Report", for example when input in cell A1 is Source1!$A$1:$C$14, but still have the same "Reference isn't valid" issue to refresh pivot when source is coming from external workbook "Source" when input in A1 [Source.xlsx]Source1!$A$1:$C$14 or U:\[Source.xlsx]Source1!$A$1:$C$14 .It looks like indirect formulas in the name manager only works when the source is within the same workbook "Report".

Attached picture shows both Indirect formulas for the Name Manager and respective inputs for A1 in green that does not work to change the pivot source located in the workbook "Source".

 

[awoohaw]

then don't use the range name from the other workbook. Can you try to put the entire path (including cell reference) in the calculation?

but maybe it is not possible for pivot reports. Do you need a pivot report? Can you create a data range using array calculations?

 

[elbarauljr]

Yes, I tried the entire path. I will explore with Power Query connections. Thank you for your help.