Hi,

I am looking for a solution to simultaneously take of advantage of the
Dynamic Range as explained by Debra :
http://www.contextures.com/xlNames01.html#Dynamic
and keep the source database in a separate workbook, different from the
one which contains all the pivot tables ...

Thanks in advance for your comments
Cheers
Carim

Put the names in source workbook. When creating the PT specify the
name rather than the range.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hi,

I am looking for a solution to simultaneously take of advantage of the
Dynamic Range as explained by Debra :
http://www.contextures.com/xlNames01.html#Dynamic
and keep the source database in a separate workbook, different from the
one which contains all the pivot tables ...

Thanks in advance for your comments
Cheers
Carim

Hello Tushar,

If I am not mistaken it is exactly what I am doing and I keep getting
an error message :
Reference is not valid ...
I guess I have missed something ...
Thanks for your help
Regards
Carim

In the source workbook, Book4 in my test, I created a name covering 2
columns and as many rows of data as are present:

myRng =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),2)

In Book5, the workbook containing the PT, in the PT wizard I specified
Book4!myrng as the source.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hello Tushar,

If I am not mistaken it is exactly what I am doing and I keep getting
an error message :
Reference is not valid ...
I guess I have missed something ...
Thanks for your help
Regards
Carim

I think I did not expressed myself clearly enough.
It works fine as long as the second workbook is open ... as soon as it
is closed, the source range is no longer recognized.
Whereas, when one uses static ranges, it works in both cases (second
workbook open or closed)
Hope I have clarified my question.

Regards
Carim

Think you need to include code in the source workbook which will hard code
the extent of the named range upon closing. If it is essential that it be
dynamic when opened, then in the workbook open event, redefine it with the
appropriate formula - otherwise, just depend on the beforeclose event to
hard code it.

worksheets("Data").Range("A1").CurrentRegion.Name = "Database"

as an example.

--
Regards,
Tom Ogilvy


"Carim" wrote in message
ups.com...
I think I did not expressed myself clearly enough.
It works fine as long as the second workbook is open ... as soon as it
is closed, the source range is no longer recognized.
Whereas, when one uses static ranges, it works in both cases (second
workbook open or closed)
Hope I have clarified my question.

Regards
Carim

Tom,

Thanks a lot ...
I will give it a try right away ...
Cheers
Carim

Tom,

Thanks for your hint ... I have now managed to hardcode the range in
the source workbook, which means I can now operate the "pivot-tables"
workbook ...
Again thanks a lot !!!
Best Regards
Carim