A am using Excel 2000 with Windows XP, and I am specifying a new data range
for an existing pivottable. I want the new data to be from a dynamic range
in a worksheet from a different workbook. Is there a problem with my syntax?
(There are 21 columns in my worksheet.)
To name the range I used =OFFSET('ORDER BOOK'!$A$1,0,0,COUNTA('ORDER
BOOK'!$A:$A),21). To refer to the range in the wizard I used '[Name of
Workbook.xls]Order Book'!RangeName
When I try this I get "Reference is not valid."

1. Since OFFSET does not work on closed workbooks the source workbook needs
to be open when you
use it or the wizard will return that error.

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"JayBee" wrote in message
...
A am using Excel 2000 with Windows XP, and I am specifying a new data range
for an existing pivottable. I want the new data to be from a dynamic
range
in a worksheet from a different workbook. Is there a problem with my
syntax?
(There are 21 columns in my worksheet.)
To name the range I used =OFFSET('ORDER BOOK'!$A$1,0,0,COUNTA('ORDER
BOOK'!$A:$A),21). To refer to the range in the wizard I used '[Name of
Workbook.xls]Order Book'!RangeName
When I try this I get "Reference is not valid."

Thank You!
Do you know if there is another way to utilize a dynamic range in this way
with the sourcebook CLOSED?

"Peo Sjoblom" wrote:

1. Since OFFSET does not work on closed workbooks the source workbook needs
to be open when you
use it or the wizard will return that error.

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"JayBee" wrote in message
...
A am using Excel 2000 with Windows XP, and I am specifying a new data range
for an existing pivottable. I want the new data to be from a dynamic
range
in a worksheet from a different workbook. Is there a problem with my
syntax?
(There are 21 columns in my worksheet.)
To name the range I used =OFFSET('ORDER BOOK'!$A$1,0,0,COUNTA('ORDER
BOOK'!$A:$A),21). To refer to the range in the wizard I used '[Name of
Workbook.xls]Order Book'!RangeName
When I try this I get "Reference is not valid."