How to update a PT based on a NamedRange of another Workbook?

[feipezi]

Hi,

I tried a few ways of doing it but failed. The message says something like "the reference is not valid".

What I did is like ...[workbookname]namedrange, or []!namedrange, or []"namedrange" and more. Nothing worked.

So what's the right syntax for it? I don't want to do things like []sheetname!$a$1:$z$100.

I haven't tried putting the OFFSET function there since the NamedRange was dynamically created. I don't think it's going to work.

Thanks in advance and have a nice day.

 

[SkipVought]

hi,

Let's go back to the worksheet where you want your Pivot Table to be added.

You'll see that when you INSERT your PT, and select and EXTERNAL source, you need a CONNECTION to whatever, not just a workbook/sheet/range.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Hi,


Are you saying that I am not supposed to use a path\filename.xlsm\namedrange and I have to go through the CONNECTION process? It's not a new PT. It's an existing one.

As I selected Options->Change Data Source, it shows something like this:

'\data\LTC\Reports\Templates\[LTC Chargeback Sales Report Template.xlsm]DATA'!$A$1:$L$46841

You can see the last part of the path. It's a worksheet with a range address. The reason why I don't like it is that each month the number of rows will change. If it was a NamedRange, I won't have to replace 46841 with a different number. The point is it's not an external data like Access or ODBC something. All I wanted is to use a NamedRange instead of the highlighted part.

Thanks again for your input.

 

[SkipVought]

What version Excel?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Excel 2010