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

Pivot column value not visible and conditional format

T

Thomas Kuriakose

Active Member
Respected Sirs,

We have a pivot summary for status based on numbers date wise.

The pivot table does not show the status values in the table. Kindly let me know why this is not visible (B5:K5).

Secondly, how can we conditional format the values in B6:K2141 based on the column headers status A and P.

If status is A - Green (Fill color)
If status is P - Red (Fill color)

Thank you very much,

with regards,
thomas
 

Attachments

  • CF Pivot.XLSX
    994.3 KB · Views: 5
Hello Thomas,

Not sure how can we get those A's and P's using a pivot table..

I have updated your file with a Summary worksheet where I have populated the values using Index & Match formulas..

On the source data..I have added 2 columns one contains the year and 2nd contains the concatenation of number and year ( this is used in the Index & Match formula)

Hope this helps...

Note: as I am not able to uploaded the file in .xlsx format due to the file size limit on this site.. I have zipped the same and attaching here..
 

Attachments

  • CF Pivot.zip
    609.5 KB · Views: 2
Respected Sir,

Thank you very much for this solution and usage of formula to arrive at the result.

Much appreciated,

with regards,
thomas
 
Respected Sirs,

I deleted the first pivot table and recreated, this time the status is appearing. I don't know as to why the status appeared this time and not in the first pivot table.

Kindly guide on how to conditional format the values in range B6 to P2141 based on headers A and P.

All values that are non blank under header A - Green
All values that are non blank under header P - Red.

Thank you very much,

with regards,
thomas
 

Attachments

  • CF Pivot.XLSX
    242.5 KB · Views: 2
Respected Sir,

Thank you so much for this solution, this is perfect.

Sir, kindly let me know how the CF was achieved in this case.

Much appreciated,

with regards,
thomas
 
Hi ,

When you place the cursor above any P , you will see it turn to a solid black downward pointing arrow. Clicking it at that stage will select all columns labelled P.

Click on Conditional Formatting , Highlight Cells Rules , Greater than , and enter 0 in the value entry box. Select the color and you are done.

Repeat for the columns labelled A.

Narayan
 
Hi ,

Place the cursor in any cell in the column labels area , say B5 , and right-click.

Click on Expand/Collapse , and then click on Expand Entire Field.

Try it on your initial uploaded file.

Narayan
 
You must log in or register to reply here.
Back
Top