• 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.

Formatting row in Pivot table based on lookup to other data

J

jturn00

New Member
Hi All,

I have a pivot table where I like my current layout but now I've been asked to add some colors to each row.

Basically, I have a row for each project and the columns list the months. The fields currently show hours.

Jan Feb Mar .... Dec
Project 1
Project 2
.....
Project 10


Now I want to color the project title in each row based on each status for the individual project. (as an alternative, the rows can be colored instead but not as desirable)

So my status is either 1, 2 or 3. (Upcoming, Ongoing, complete).

Each project has a status on a that can be looked up in the table that created the pivot. (I can add it to the pivot but then I get nonsensical rows of 1, 2 and 3 in the report that wouldn't be understood.)

Any thoughts?


Regards,
Jeff

P.S. I'd prefer not to use VBA since executives might use this and not have VBA enabled or know how to run it.
 
Hello Jeff,
How about adding the status column to your pivot table, and use conditional formatting to color the project name column based on the status column, and then hide your status column?

Feel free to post a sample file with data, and we can demonstrate this concept if needed.

Cheers,
Sajan.
 
Sajan,

I've been working with this and haven't been able to get it to work. Here is the sample sheet. I am using a variation of one of Chandoo's templates and I am assuming an member is associated with only one activity. So the legend page has the activity and code (i put this in). Now if I do a lookup in the master table for activity (either by code or title. How can I get the member (or entire row) shaded based on the activity.
 

Attachments

Jeff,
When I put a conditional format on cells B7:B18, with the formula =$C7="Have Fun" it worked for me. You can setup additional conditions for your remaining activities.

Is that what you are after?
 
How did you get the Have Fun in to C7? If I add the activity code it changes the size and format. Do I need to add the activity code to the pivot table or is there a way to look up the member info from another table to do the condition. Based on what you added, I need the activity code in the columns which changes the weeks across the top.

See the attached for what I am looking to do. Here I manually formatted the rows. (which is how we are doing it currently but everytime the table updates, it changes.)
 

Attachments

I added the "Activity Code" into the Pivot table, and set the layout to "show item labels in tabular form". I then applied the conditional formatting. After you add the conditional formatting, you can always hide the activity column, if you don't want it displayed.
 
You must log in or register to reply here.
Back
Top