• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Keep pivot table structure stable

M

Milky

New Member
Hi all excel experts, I'm a fan of excel and pivot tables but right now I can't solve one problem with keeping the structure of a pivot stable even after data updates.


So the thing is, I have a set of data (pipeline) and some predefined pivot tables. one of them shows data by product groups and months. this data is to go to a summary file (column A - product groups with subtotals, columns B-... moths, outlook data inside), and it would be practical if these pivots were always in the same format, so that one could easily copy-paste or sum data across multiple similar pivots.


now, when i set up the pivot the way my outlook file is structured, upon update it changes the structure if some product groups are not present in the database (i.e., there are no projects on the given product groups). in this case I want the pivot to preserve the row with the product group but show zero in all months. and I dont want to always have to keep some "dummy" zero rows in my database since the people who will actually update it are likely to delete them.


any help is highly appreciated!
 
Milky

Have a read of: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hui, thanks, here is the file


http://www.speedyshare.com/files/24599191/sample_pivot.xlsx


so what i want is that a pivot based on the data on sheet "projects" had the layout I have on sheet "Outlook".

as you can see, the pivot I got skips rows with zero values, but I want the layout to be consistent and be able to only change the "projects" tab and then press Refresh to get the data in a pivot in the needed format.
 
Yeah this is a little tricky. Dummy columns would obviously be the easiest. Maybe have your display sheet with getpivotdata instead of just the pivots?
 
Milky

Problem is that you don't have data for

Product 2.1, 3.1, 3.4 & 3.5

easiest way would be to add blank rows at bottom of data table and refresh

eg:

[pre]
Code: 
Region 3    KAM 4    Product 2.1    Products 2    M1    0.00
Region 3    KAM 4    Product 3.1    Products 3    M1    0.00
Region 3    KAM 4    Product 3.4    Products 3    M1    0.00
Region 3    KAM 4    Product 3.5    Products 3    M1    0.00
[/pre]
Highlight them a different color to remind you what they do


I would also goto Pivot table options and set "for empty cells show:" and set 0.0


Note, in your projects table

Products 4.x has 2 spaces between the t and number

Products 2.x and 3.x only have 1 space
 
Hui,


thanks but the thing is to do it without these dummy rows. is there no way to do it?


since it's only a sample table, i think it doesnt really matter how many spaces there are, but thanks)
 
Milky

The problem is that without the dumnmy rows, the Pivot Table has no way of knowing where to start/stop putting in a sequence of labels ie: Excel can't know to stop at Product 3.5 unless it has a record to do that.


I have 2 suggestions


1. Put the dummy rows at the top of the table and then hide them

2. Make a "Pivot Table" style report manually using a combination of carefully structured named ranges and sumproducts
 
Sorry. I'm less attentive on Sundays. It's football season, you know.


Create another sheet, have your values fixed on the left hand side with the get pivotdata forumula having the values. Preface your getpivotdata forumula with an IF(formula=N/A,0,formula)
 
You must log in or register to reply here.
Back
Top