Dynamic Ranges and Pivot Tables - Say It Ain't So

[schielrn]

I have just upgraded to 2007 at work and I have many pivot tables that use dynamic named ranges and not all the references are saying they are not valid? I can hardcode a named range and it works fine. I also click on the named range and it highlights the correct area, but the pivot tbale will not accept this.

Do I have to accept the fact that this is no longer do-able in 2007 via a dynamic range formula using offset or indirect?

Thanks,

Rob

 

[Richard Schollar]

Hi Rob

Are these 2003 files being opened in compatibility mode? It works OK when I use DNRs with PTs in 2007 files.

 

[schielrn]

Yes they are 2003 files opened in compatibility mode.

I have also tried saving these as 2007 files and they still didn't seem to work, but I am not at work to test it until tomorrow.

 

[schielrn]

I still have had no luck with this in my 2007 spreadsheet, but it seems I have only noticed it with this large file (~25 MB). I can hardcode the range, but it takes probably 2 full minutes to calculate. But I can't see anything wrong with this formula:

=OFFSET(MetricsData!$A$4,0,0,COUNTA(MetricsData!$A:$A),COUNTA(MetricsData!$4:$4))

When I click in it, it appears to be selecting the correct region, but looking at the defined names list, my data2 (which is the hard coded range) has under the Value column {"StratDiv","Period",...etc.), but data1 which is my dynamic range has just {...}.

Has anyone came accross this? I have done some research online and it seems like no one has came up with a solution?

Thanks,

Rob

 

[schielrn]

Ok I tried in a completely blank workbook and set up a very small table:

Name Amount
Rob 4.91264
Joe 1.234651
John 0.381376
Jake 2.624567
Steve 1.06645
Zach 2.103037
Chris 0.043408
Rob 4.251038
Adam 1.361116
Rob 2.868785
Zach 2.916356

I then set up a simple DNR:

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

When I look at the value in teh name manager it again has the {...}. But when I select the area in the Refers to: it selects the correct range?

Does anyone know what I am doing wrong? Do I need to do something with scope?

Thanks,

Rob

 

[schielrn]

Ok it seems maybe that the dynamic ranges wouldn't work in compatibility mode? Not positive on that, still testing.

But with the large file I was having problems with before, if I saved it in 2007, then created teh dynamic range with a new name, it appears to have worked. Not sure why this is and it still seems to be a pain.

I have now ranted on long enough in here.