Pivot Table Fields (Fill Down) Problem in Excel 97...

[analyst44]

Hi.

I found the solution I am looking for to my problem on this site, but it only works in Excel 2000 or higher. I need to use Excel 97 for a company wide project at work and am wondering if there is any conceivable way that I can carry out the following in a different way...

It may even involve another pivot table or cell reference, but it's got to dynamically update with the pivot table.

Here is the problem:

I have a pivot table in a format that puts a key driver in a column with another column next to it that has the year in descending order and then months across the top so that I can get a snapshot of each key driver by year by month. Looks like:

....................................Month 1......Month 2.....
Key Driver 1...... Year 1
...................... Year 2
...................... Year 3
Key Driver 2 ..... Year 1

I need "Key Driver 1" to repeat in every row until "Key Driver 2" shows up because when I link this table to cells for charting later on, it has to be associated with it. This cannot be done with formulas on the other sheet because those formulas get outdated as soon as a new year is added (or one is taken away) from the data that the pivot table is based on...

The solution for this for this was given as follows, but I cannot change a part of a pivot table in Excel 97 so this does not work:
--------------------------------------------------------------------------
But here is the real tip for this week. Jennifer writes "I have continually run into the problem of using the pivot table option, to summarize reams of data, but it does not fill in the rows beneath each change in row category. Do you know how to make the pivot table fill in below each change in category?? I have been having to drag and copy every code down so I can do more pivot tables or sorting. I have tried changing the options in the pivot table, to no avail."

The answer is not easy to learn. It is not intuitive. But, if you hate dragging those cells down, you will love taking the time to learn this process! Follow along - it seems long and drawn out, but it really really works. Once you get it, you can do this in 20 seconds.

There are actually 2 or 3 new tips here. Let's say you have 2 columns on the left which are in outline format that need to be filled in. Highlight from cell A3 all the way down to cell B999 (or whatever your last row of data is.)

Trick #1. Selecting all of the blank cells in that range.


Hit ctrl+G, alt+S, K and then enter. huh?
Ctrl G brings up the GoTo dialog
alt+S will pick the "Special" button from the dialog box
The Goto-Special dialog is an awesome thing that few know about. Hit "k" to pick "blanks". Hit enter or click OK and you will now have selected just all of the blank cells in the pivot table outline columns. These are all of the cells which you want to fill in.
Trick #2. Don't watch the screen while you do this - it is too scary and confusing.Hit the equals key. Hit the Up arrow. Hold down Ctrl and hit enter. Hitting equals and the up arrow says, "I want this cell to be just like the cell above me." Holding down Ctrl when you hit enter says, "Enter this same formula in every selected cell, which, thanks to Trick #1 is all of the blank cells which we wanted to fill in.

Trick #3 (which Jennifer already knows, but is here for completeness) You now need to change all of those formulas to values. Select all of the cells in A3:B999 again, not just the blanks. Hit ctrl+C to copy this range. Hit alt+e then sv (enter). to Paste Special Values these formulas.

Ta-da! You will never spend an afternoon manually pulling down column headings in a pivot table again.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.
-----------------------------------------------------------------------------

If someone can help on this I'd really appreciate. My only other recourse is using very long and complicated indirect () functions that will require a lot of time to program properly.

Thanks!

 

[RalphA]

I have Excel97. This is what I did, and it all worked just fine.

Trick #1. Selecting all of the blank cells in that range.
Hit ctrl+G, alt+S, K and then enter. huh?
Ctrl G brings up the GoTo dialog
alt+S will pick the "Special" button from the dialog box
The Goto-Special dialog is an awesome thing that few know about. Hit "k" to pick "blanks". Hit enter or click OK and you will now have selected just all of the blank cells in the pivot table outline columns. These are all of the cells which you want to fill in.

Trick #1:
Highlight the whole column
Ctrl+G, click on Special...,instead of Alt+S,
then click on the Blanks option button, instead of the K,
then click on OK
You now have the blanks in that column selected.
So much for "Trick 1"

Tricks 2 and 3 work fine, just as Jennifer explained in your post.

 

[analyst44]

Hi RalphA

Thanks so much for responding. I understand what you are saying by clicking on the physcial buttons rather than using hte Alt-X shortcuts. That's not the issue, though. I can select all the non-blanks just fine.

However, when I attempt to do what you said or what Mr. Excel said in his response to Jennifer in a column that has Pivot Fields in them as described above, I receive an error as these fields are not allowed to be edited in Excel 97. There is on cell for each "group" and the rest mus remain blank. Therefore, Key Driver 1 is listed, followed by blank cells in that column until it hits Key Driver 2

This does not happen in Excel 2000 as these fields are editable.

Perhaps there is an option in the Pivot Table Menu that allows fields to become editable, but I have not been able to find it.

As it stands, I've found a way around it using formulas in my other table, but I am still curious if this can be avoided in the future.

Thanks so much.

 

[seancotten]

The tip only works for copied pivot tables...actual pivot tables can't be edited no matter what the version.

 

[analyst44]

i beleive you can type something into the fields in excel 2000 and above. say i have a two columns:

salesperson territory
mike east
west
south
north

in 2000, you can type mike into the other three rows that have the territory so you can do more analysis. this cannot be done in 97.

to get around it, you can put a column to the left and use a =if(isblank()) formula to get what you want. it's just annoying to have to do that when dealing in 97.