Issue with - Pivot Tables using Dynamic Ranges

[mahowarth]

Hi Guys,

Worksheet A - I've set up a dynamic range to display the last 12 row entries of 2 columns
Worksheet B - pivot table of the range

The dynamic range is working perfectly, it displays the correct rows
I can set up the data source once... the results on the pivot table display corectly

Problem
When i press refresh, the data source "dissapears" and i have to rebuild the pivot table.


Please help with any solutions?

Regards
Mark

[noboffinme]

Can you post the file?

Thanks

[mahowarth]

If I refresh or add any addtional data on Sheet1 - the pivot breaks.

Day spreadsheet.xlsx

[noboffinme]

Hi mahowarth,

It looks like you want the Pivot table to show a 'Rolling' report of the past 12 dates.

If this is correct, I've created a solution in the posted attachment.

What I did was to create a Pivot on your source data.

I then sorted the dates column of the Pivot as Newest to Oldest (Right click in the Pivot for this option)

Then right click again to select 'Top 10' & change the Show total number from 10 to 12.

Because you didn't have the headings included in the Pivot tables source data, you had this problem.

Let me know if this isn't what you wanted, Cheers.

[mahowarth]

Thanks noboffinme!

I see how it's sorted - however I needed it to show the last 12 rows of data, the Top 12 items filter provides me with the top 12 items based on the "total" column.



29/04/2013 5
27/05/2013 8
24/06/2013 6
22/07/2013 4
19/08/2013 4
16/09/2013 3
14/10/2013 7
11/11/2013 8
8/12/2013 8
9/12/2013 8
7/01/2014 6
8/01/2014 4
22/01/2014 2



I appreciate your help so far and future assistance!

[noboffinme]

It may help if you tell me what this is for/what you're trying to do.

I'll then try to help further.

Cheers

[mahowarth]

Hi noboffinme,

I want to pivotchart the last 12 entries of the date and max days.
to Pivotchart, i need a pivot table, to display the last 12 enteries

How do i do that when i keep adding entries to the data source?

Regards
Mark

[noboffinme]

Hi Mark,

This set up I sent should do this for you.

The pivot is set to show only the Top 12 sorted by dates Newest to Oldest.

Try adding some new data & see what happens.

[mahowarth]

Hi Noboffin,

I appreciate what you have set up, however when it displays the Top 12 items, the top 12 are not based on dates, but the values that in Max Days... so depending on the data i put in, i could have over 12 or under 12 items.

You can test the issue but putting in the date of 1/2/2014 with the value of 1 in max days into row 18, columns A and B.... refresh the data and it should appear in the pivot table but it doesn't.

[noboffinme]

Hmmmm, I see what you mean...

Question, when you were only reading the last 12 rows on your source data, why couldn't you just delete the rows you didn't want in the Pivot?

I'm sure you had a reason, I just need to know how you're getting the source data & why it comes in such a way.

I've tried a few things with the Pivot (none worked as yet).

So that's why the questions.

Cheers

[mahowarth]

The team using the spreadsheet are really um (lacking) of all excel skills... so i figured they cant go wrong if they only have to enter cells and then hit refresh.


The pivot keeps breaking because the dynamic name range doesnt have a heading.... hmmm


Thanks!

[mahowarth]

Anyone else with some time to have a look ?

it is much appreciated.

[Izandol]

I will suggest that because you only want twelve entries you create a new table using formulas to return the last twelve entries from the raw data and then just pivot from this table which will never change size or location. You must then only refresh the pivot when new data is added.