Dynamic Range with another workbook

[Carim]

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

[Tushar Mehta]

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 <[email protected]>,
[email protected] 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
>
>

[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

[Tushar Mehta]

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 <[email protected]>,
[email protected] 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
>
>

[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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Carim]

Tom,

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

[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