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

Is there a way to create conditional formatting based on a police officers rank?

cricket1001

Member
I have a workbook for all of our police officers employee info. I would like to somehow do a conditional format for the whole workbook where depending on the officer's rank including civilians would be color-coded with a specific highlight and specific text for all of their info across the rows. So like all "Patrol Officers" would have a light blue highlight with a dark blue text color. Then if an employee was either demoted or promoted to a different rank, all their info would change to that rank's highlight and text colors. It takes so long for me to do this across multiple worksheets and multiple rows of cells.
 

Attachments

  • Example 1-Formatting.xlsx
    27.5 KB · Views: 7
Yes it is reasonably straightforward, though a little tedious to set up the multiple formats/ranks.
I still think you should convert your tables to Excel Tables in order to have a meaningful way of referencing data and to ensure that formulas and properties adjust as you add rows to the Table. That allows me to use a defined name 'Rank' which refers to
=Table1[@Rank]
and means your CF conditions may be written
= Rank="Sergeant" etc.
without worrying that the cell selected while you are typing the formula is A4. The fact that the references are relative can make an utter mess of formatting if you are not careful while setting it up.
 

Attachments

  • Example 1-Formatting.xlsx
    29.6 KB · Views: 12
I am going to create a new worksheet next to my current worksheet and create the table as you suggest. I'm thinking that I tried doing this with a different worksheet and there was a problem I had that I didn't have when I didn't use the table structure. But I don't remember what it was. Maybe in doing this using a table I'll see what problem I had and share it with you in order to fix the problem.
71700
Well, maybe my problem was that when I go to Insert>Table, I get the above window. But it won't let me choose the worksheet where my data is. It also won't let me just create a table where I just put in the number of rows and columns I want, like I do in Word 2016. I am seeing that I need to learn how to tables correctly in Excel. I am very confused how you create a table without being allowed to go to different worksheets and maybe workbooks where your data is. Hmmm, I'm stuck already!

I opened your attachment but am not sure if there are any changes made on it from my attachment. The size of your attachment is a little bigger than mine so I thought maybe you changed it to a table or did something to increase the size. But I don't see any changes. How can I tell if you created a table from my info?

I just want say how very much I appreciate your help, and everyone's help, in my quest to get better in creating workbooks and worksheets and hopefully I can pass along the help to others!!! :)
 
So, after my previous message I went back to my worksheet and went to Insert>Table in that worksheet and then highlighted all the cells including the headers and then clicked on the "My table has headers" option. But after I clicked on "Ok" I got the following message.
71701

So now what did I do wrong? What other table is this message referring to? I didn't do this in the worksheet that opened from your attachment. I did this in my original worksheet. Oh, I am so confused.
 
I had already converted the data on the 'Format' sheet to make it a Table - hence the message! To create a new table from existing data you either select a single cell and allow Excel to identify the table to create, or you can select the entire range to be occupied by the table.

As I have already converted the data to a Table, you could have a play before turning to the conditional formatting. Try redefining some of the formulas by clicking the referenced cells. Doing this should change references such as $D5 to [@DOB], moreover, it is not just the cell that changes, it is the whole column formula.

Then if you go to Conditional Formatting / Manage Rules... you will find work to do.
Finally, you can start over and do it with your actual data ;)
 
I should have played with the table before my last post. I did like you suggested and changed the "$D5 to [@DOB] " and I was amazed that it changed the whole column to that formula. I'm so excited that this would save me a lot of time. I am wondering where I could find tutorials on saving time while designing workbooks.
 
I should have played with the table before my last post. I did like you suggested and changed the "$D5 to [@DOB] " and I was amazed that it changed the whole column to that formula. I'm so excited that this would save me a lot of time. I am wondering where I could find tutorials on saving time while designing workbooks.
And if you wish to add another Officer to the table either press 'tab' when you are in the bottom right cell or simply start typing in the next row. All the formulas and formatting will be applied to the new table row and any references or charts will grow automatically,
 
Thanks so much Peter!!! Is there a way I can copy or send this whole thread to my Word 2016 for me to keep handy while I am changing my worksheets info into tables and so I can go back to it when needed? I'd love to be able to highlight the most important things.
 
Back
Top