Pivot Table Help

[morerockin]

Hello All,

Please See attached.

I have project KPI's in column E, G, and I in the project tab. If the project is off course, it will red RED.

I am simply trying to get my pivot table to show me cleanly only my projects with red KPI's.

Example

First column in Pivot: PM
2nd column in Pivot: RED KPI's (for KPI)
3rd column in Pivot: RED KPI's (for KPI2)
4th column in Pivot: Red KPI's (for KPI3)

I kind of have it setup that way but I am getting a lot of data I don't want/need.

[shirleyxls]

The really simple way of doing this is to change your KPI formulas so that they show a 1 instead of the word "Red" if that would be acceptable?

You can then simply do a SUM of the values in each KPI in your Pivot values.

You could also change your CF rule so that the cell is red if the value is 1 (if you change the font to the same colour you won't even see the 1)

[morerockin]

Ok . . I tried that and I don't really think it did anything in terms of cleaning thins up.

Ultimate goal is to have a clean view of all 3 KPI's that are Red.

See attached.

[morerockin]

Ok . . I tried that and I don't really think it did anything in terms of cleaning thins up.

Ultimate goal is to have a clean view of all 3 KPI's that are Red.

See attached.

Help please? :'(

[shirleyxls]

I've attached an updated version of your workbook.

First I removed your duplicate header row on the project sheet which may have been causing some of your problems. I also deleted the first blank row as this keeps the data "clean".

I also modified the formula to set the KPI value to 1 if the previous column is 0. You didn't really need both checks in there.

The new pivot table does a SUM of the KPI columns and now shows the figures for those which are Red.

By the way, did you realise that you have auto-calculation switched off? Threw me a bit when I was trying to get the numbers to come out

Hope that helps

[morerockin]

Hey Shirley,

I appreciate you looking at this.

So I have a data connection established for this report so when I need new data input, I go to the Data Tab--->Refresh All---> Refresh. It then generates a new report. However, the two rows you removed reappear. So I added the duplicate row and populated column titles so I could get it to go into pivot table.

That being said, I am looking for an automated way for the pivot to update without having to delete rows.

I have autocalc switched off because I have another spreadsheet thats 100MB that takes 2 min to calculate.

What do you think?

[shirleyxls]

Hi

Glad it helped a bit

I'm a bit rusty on XML imports (which is what I think you have for your data refresh), but you might be able to modify that so that it starts from Row 3 if the first two are header and blank rows. You can certainly do that with csv imports (sorry - most of the work I do is still using that format!).

Another way to make it a bit more automated and for your pivot to take extra rows into account without needing to change the range, is to convert your project sheet to a table. Your pivot would automatically expand with your table and the table will automatically apply formulas and formatting to new rows so another bonus.

I also often use a very simple piece of VBA to automatically refresh pivot tables on a worksheet. You can put this as an event on the pivot table worksheet so that it automatically updates every time you activate that sheet:

Please Login or Register  to view this content.

Now I see why you had autocalc switched off

[morerockin]

Sweet. I have a few questions!

"you might be able to modify that so that it starts from Row 3 if the first two are header and blank rows"

So the information I am importing, I can possibly tell Excel to NOT import the first 2 rows and start from row 3, right? I will tinker around with that to see if i can get it to do that.

" is to convert your project sheet to a table"

Yes, How do I do this! I am surprised it didn't do this automatically.

[shirleyxls]

I think you might be able to tell Excel NOT to import the first 2 rows, but as I say I'm a bit rusty on XML. Worth having a play around with though. If you can't get it to work, let me know as we might just be able to ignore those rows in the pivot table by filtering them out.

To convert your project tab data to a table, select any cell in the range, got to Insert and select Table. A Create Table dialog will appear with the range selected. Tick the My table has headers box, then OK. Hey presto, you're range will now be a table! The only problem when I tried this was that I get a warning that all external connections will be removed! Probably not what you want, so this might not be the right solution for you.

I have to got out for a while, but if you have a play around with the extra row issue I will have another look at the table problem when I get back

[morerockin]

Thanks Shirley.

I was able to get Excel to import the data as a table so I have the table part taken care of. I'm now still fishing around to find out how I limit the range of what is imported so we can get rid of the pesky rows.

[morerockin]

I found a workaround.

Now . . I think this could be the last issue.

When in the pivot table you sent me, look at cell D12. When you click on that, it should only open up one row in the new sheet but it doesn't. I am having this same issue in my sheet. What is the fix on that?

[shirleyxls]

Glad you got the table sorted - I seem to remember that XML is fine to import direct to a table format.

I've had a look around to see if I can figure out how to stop those additional pesky rows - no joy yet but I'll let you know if I come up with something. If you've already found a solution, I'd be very interested to hear about it as it's bound to come again somewhere else!

[shirleyxls]

Sorry, just seen your other comment... let me take a look and get back to you.

[shirleyxls]

Do you mean if you right click and select "Show Details"?

I don't think there's a way of only opening that one instance as Excel just shows all the details relating to the row.

The only thing I can suggest to make it easier to see which Customer the KPI relates to is to move the Customer field down into the Rows area on the pivot table (under PM). This then splits the KPI totals between each customer so you can see more detail. If you then go to the Design tab and play around with the Report Layout options, you can get it to look quite neat. You can also play with the Subtotals settings to move them above or below the data (or take them out completely).

On the Analyze tab on PivotTable Tools you can also Collapse the PM field so you just see the totals - you can then just expand to see more detail on the one you want.

You could also put your Region field down into Rows as well, but there are a lot of blank and N/A values so it looks a bit messy.

I've attached another update with a new sheet "Another Pivot". This one also has my VBA script, but you'd need to save it as an xlsm to run it.

[morerockin]

So in Pivot Tables that I use in other sheets, when I click on the value (I'll use my example in my previous about Sandeep), it would bring up only one row as that is the only item contributing to that sum.

I need it to have the functionality that when someone clicks on their total Red KPI's that it only opens the rows that are actually red KPI's in the new sheet.

How do we get rid of all of the other items that pop up on the list?

[shirleyxls]

I've done a bit of research (and a lot of playing around!) without much success I'm afraid. I'm think this may be something to do with having three separate columns for the KPI's.

The only potential solution I managed to come up with was to put a helper column in your project sheet which sets a value to "Red" or "OK" depending on whether there are ANY of the 3 KPI columns which are set to 1 (red).

I then built a new pivot table using just this one column and used the filter to display on the ones with "Red" as the value. If you double click on any of these values, you only see the ones which resulted in a red KPI, but it could be any of the 3 KPI's which make it appear on the details.

I've attached an updated workbook to show you how it works.

If this not going to fit the bill, then I guess we're back to the VBA route

[morerockin]

Hey Shirley,

This could work, the only problem is I have to account for blank values. Example, delete the value in D2, F2, or H2, and column J will show RED. If the value is Blank, it should return OK instead.

[shirleyxls]

Now we have gone a full circle

If you can have blanks in those cells, we need to put back what I think was your original formula in the KPI columns:

=IF(D2="","",IF(D2=0,1,""))

That way, we're being very specific about the 0 value (and ignoring the blank)

I tried it on the copy of the last file I sent you and it seems to work OK... fingers crossed

[morerockin]

Hey Shirley!

99.9% there!

Last question, how do I get rid of the Grand total column in the pivot?

[shirleyxls]

An easy one to finish

Click anywhere in the pivot table, then go to the Design tab on the PivotTable Tools. Over to the left you will see buttons for Subtotals and Grand Totals. If you click on the "Off for Rows and Columns" it will switch off all totals.

[morerockin]

Solved!!

Thank you!!!

[shirleyxls]

My pleasure. I enjoyed the challenge

[morerockin]

Hey Shirley

I'm back

So what we previously discussed does exactly what I want! However when I demonstrated this report for my peers, they were not satisfied. The issue comes from where you double click on a value from the pivot and it opens the data in a new sheet. Example: if we clicked the number of red KPI's next to the persons name, they wanted to see more than the number "1" to represent the red KPI.

Is there anyway to create some sort of formatting in that sheet? (the sheet that is the result of you double clicking the corresponding value in the pivot table.

[shirleyxls]

Hi there,

I'd forgotten all about this one I still had copies of all the files though and read back through the posts to refresh my memory.

Unfortunately, the double click on the item in the pivot is a built in Excel function and it literally just pulls out the data details which make up the pivot table so no quick, simple way to change this.

What information do the others want to see? The only thing in the original data is the "1" to indicate a Red KPI and the extract on double-click pulls through all the other fields on the project tab so not sure what else you could show.