Pivot Table vs dynamic table

**bimo** · 06-18-2013, 10:40 AM

Hello

I create filtered table daily, as a summary, based on refreshable report Sheet “All Orders”

, I have been using Pivot table so far, but as one Colum “reasons” is added when report is refreshed, I need to play with the pivot table for a bit before I get the final results.

Is there a quicker way maybe with a cooler result >? I hear of Dynamic tables but I am not sure how to go about that ..

Would appreciate any suggestions

Thanks for reading
Refreshable report with Table.xls

**MarvinP** · 06-18-2013, 11:19 AM

Hi bimo,

I took your sample and created a Dynamic Named Range of cells on your orders sheet. You will need to find the Names Manager to see the formula of:
=OFFSET('All Orders'!$B$6,0,0,COUNTA('All Orders'!$C:$C),22)

I named this range "DNROrderTable".

I then went to your Pivot Table and changed the Data Source of the Pivot. I changed it to "=DNROrderTable" and refreshed the Pivot.

Now you can change the amount of data on the order table and have the answer reflected in the Pivot You will need to refresh the pivot to get an answer.

See the attached and look at the Names Manager and Source of data for the Pivot.

See http://www.ozgrid.com/Excel/DynamicRanges.htm or
http://www.bettersolutions.com/excel...G820716330.htm for more info on DRNs.