pivot table on expanding data range

[sedgely]

i have created an Excel Pivot Table/Chart based on data from a MSQuery Query. My question is this: when i refresh the Data the number of rows included in the results will have increased, is there a way to tell the pivot table to automatically detect the extra rows? i can update the pivot table by using the wizard and changing the range, but it would be nice not to have to do this manually.

Thanxs

Craig

 

[SkipVought]

Hi,

Check this thread thread707-692141

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sedgely]

Cheers Skip
Just off home now, so will check it out in the mornin'

Craig

 

[ETID]

You can select the just Col Lables,..A through ?


then filter out blanks in the pivot

 

[sedgely]

Thanks ETID
have tried that and it works

Craig

 

[xlbo]

1: You can get the query to go directly into the pivot table
2: If that isn't an option, you can name the range that MSQuery returns data to. This named range will expand and contract as the query returns different numbers of rows - reference this named range in the pivot table.
3: Use Skip's suggestion of dynamic named ranges (though you may as well name the external data range and use that...)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

ETID,

The Column Range method is OK

EXCEPT...

if you're using DATES and want to Group on DATE.

If you have BLANK DATES it really screws up Group in a PivotTable!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com