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!
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!