Conditional Formatting

[Taryn A]

I have an Excel workbook with a lot of dates that are colored coded as follows

Red indicates that the date in the cell is 13 months past the date entered in the cell

Orange indicates that the date in the cell is 1 year past the date in the cell

Green indicates its within the year of the date entered in the cell.

As the workbook is set up now i have to manually change the color of the cell.

i know there is a formula using conditional formatting that can automatically change the date but I'm not sure what that formula is. I will need this for most of the workbook. Can anyone help?

 

[Taryn A]

i think! I may have covered the most of the colour coding

I have added a column V and if "inactive" is in that column - then the ROW goes blue - so you could use a column to mark inactive
Maybe you could put inactive into the MI Column or any code meaning inactive and that would turn blue

the cleared Yes ,No

i have added the rules for cleared

"YES" as all green dates and 1 in FULLs and FIT = pass
"NO" any of the red dates and any 1 in any of the three failed columns

not sure what I do with Yellow or Orange in the cleared column ?

needs a lot of testing !!!!!

https://www.dropbox.com/s/v7x07halg93hc3s/Medical%20Physical%20ETAF_2%20with%20Blue%20Colour%20Exp_etaf_v3.xlsx

Hey there, I wanted to respond, but I haven't had a chance to look at the spreadsheet thoroughly just yet, but will try and do that this weekend or the first of next week. Ugh there is just not enough time in the day lol!

Inactive
I see the V column, which will work for now. I can't put anything in the MI column. Sometimes we need to use that column since we often have names that are very similar; this helps decipher who is who. What do you think about using an * after the date if the person is inactive? So if there is an * after the date the row would be blue. I could put the * in the first date column or both. Would that be possible?

Cleared Yes, No
As far as the yellow and orange in the cleared columns, those would be under Cleared “Yes” since they are either limited (yellow) or out of date (orange).

Name
Would it be possible for the Last, First & MI columns (columns A, B & C) to be coded the same colour as the cleared column? So if the cleared column is coloured orange columns A B & C would be coloured orange, and so on.

 

[Taryn A]

I have created an updated version here

https://www.dropbox.com/s/3cabhbqdzvfhith/Medical%20Physical%20ETAF_2%20with%20Blue%20Colour%20Exp_etaf_v4.xlsx

This only conditional formats the column S if green and T if red - the old version change both columns

I have enclosed the data into a table and made the style plain
now you can add members to the bottom and the formulas in S and T will copy and the conditional formatting will also copy down
OR you can insert columns and the formulas in S and T copy down and retain the conditional formatting

I meant to ask you in my last post why are there drop down arrows now? Is that something that needs to be there? I'm not liking that lol!

I'm not sure what you mean when you are saying you enclosed the data into a table and made the style plain?

I see that your formulas aren't over to side like they were. Now they are in the cleared columns?

So if I want to copy your formulas and paste them into my workbook how would I go about doing that? That is what I'm going to have to do to really test it. I just wanted to get all the formulas straight before I did it.

Thanks once again for being patient with me!

 

[etaf]

theres quite a lot going on now , in the conditional formatting
anyway, i think this covers all conditions - added the Orange and Yellow to the cleared section and to set to 1
also to colour columns A,B & C red/yellow/orange/green

as i say an awful lot going on now

we could add an * and test for that character - can you add to the name or to the MI column rather than a date ....

see this version
https://www.dropbox.com/s/k472dnfcjv3gk16/Medical Physical ETAF_2 with Blue Colour Exp_etaf_v5.xlsx

i would not be surprised if an error occurs
the order is important and to copy to a different spreadsheet now maybe difficult

the order of the conditional formatting is important to work correctly and you have separate conditional formats for most columns or groups sections

 

[Taryn A]

Hey there, I'm sorry for taking so long to get back to you. I actually tried to reply to you on Saturday evening and had my reply all typed out and my computer for some reason locked up so I had to exit out and I got back in and tried again and it still wouldn't send the message. It's either I was having issues with the internet connection or Mr. Excel doesn't like me because I log in and type my reply and hit send it logs me back out again before I can send the message.

At any rate, I haven't had a chance to look at the above spreadsheet, but hopefully I can do that tomorrow. I did some testing with the spreadsheet you sent me before this one. In doing so, I added a few rows at the bottom and when I entered the date in the date column the Date format is dd/mm/yyyy, but it should be mm/dd/yyyy. Is that something I will have to manual change or can you correct that? The format I’d like is under format cell “Date” mm/dd/yyyy and has an * beside it.

As you know, when the date column(s) are blank (no date entered) the colour should be red which is exactly how you have it. I'm not sure that I mentioned this, but in addition to the blank date column(s) being red the full column(s) (right next to the date column) should also be red even though there won’t be a 1 entered in the full column. Same goes for the Fit Test date.

If you could make it so if an * is placed in the Last name column (at the end of the name) the row would be coloured blue to indicate they are inactive that would be great. I'd rather not add any other columns if at all possible since there are so many already lol! If that's not doable, we can leave it like it is.

I know I’m pushing my luck with this one, but I figured I’ll ask since you have been so good at figuring everything out! As I'm sure you have noticed I place comments in the cleared columns wherever the 1 is entered. Would it be possible for the comments to automatically move over to the column where the 1 is placed?

With regards to copy the formulas and conditional formatting, I actually have a large workbook that has many tabs that I need to apply this formatting to. I would prefer not to have to reenter the data into another spreadsheet especially since there are so many dates that could be transposed. Is there anyway possible that I could copy and paste the conditional formatting and formulas as long as I put them in the correct order? I can view the formulas on the spreadsheet you sent me correct? If I can shouldn't I be able to copy and paste them into the workbook as long as you let me know what order they are supposed to be in and I put them in that order? I'm just afraid that if I have to reenter all that data something will get missed for sure! Would I be able to copy and past each sheet into the new sheet with your conditional formatting without messing it up? If I could do that I'd be okay with that. I just don't want to have to reenter all the data manually. Your thoughts?

Thanks again!

 

[etaf]

At any rate, I haven't had a chance to look at the above spreadsheet, but hopefully I can do that tomorrow. I did some testing with the spreadsheet you sent me before this one. In doing so, I added a few rows at the bottom and when I entered the date in the date column the Date format is dd/mm/yyyy, but it should be mm/dd/yyyy. Is that something I will have to manual change or can you correct that? The format I’d like is under format cell “Date” mm/dd/yyyy and has an * beside it.

That because I'm UK and reformatted
select the dates and then
format
custom
and put in type

MM/DD/YYYY

updated to US format in version 6 attached

I know I’m pushing my luck with this one, but I figured I’ll ask since you have been so good at figuring everything out! As I'm sure you have noticed I place comments in the cleared columns wherever the 1 is entered. Would it be possible for the comments to automatically move over to the column where the 1 is placed?

I think this would need a macro to move the comments from a cell into the comments - not sure how to do that

If you could make it so if an * is placed in the Last name column (at the end of the name) the row would be coloured blue to indicate they are inactive that would be great. I'd rather not add any other columns if at all possible since there are so many already lol! If that's not doable, we can leave it like it is.

added to the version 6 attached

As you know, when the date column(s) are blank (no date entered) the colour should be red which is exactly how you have it. I'm not sure that I mentioned this, but in addition to the blank date column(s) being red the full column(s) (right next to the date column) should also be red even though there won’t be a 1 entered in the full column. Same goes for the Fit Test date.

done

With regards to copy the formulas and conditional formatting, I actually have a large workbook that has many tabs that I need to apply this formatting to. I would prefer not to have to reenter the data into another spreadsheet especially since there are so many dates that could be transposed. Is there anyway possible that I could copy and paste the conditional formatting and formulas as long as I put them in the correct order? I can view the formulas on the spreadsheet you sent me correct? If I can shouldn't I be able to copy and paste them into the workbook as long as you let me know what order they are supposed to be in and I put them in that order? I'm just afraid that if I have to reenter all that data something will get missed for sure! Would I be able to copy and past each sheet into the new sheet with your conditional formatting without messing it up? If I could do that I'd be okay with that. I just don't want to have to reenter all the data manually. Your thoughts?

you can copy to a new sheet
IF the columns are identical layout
then you should be able to use the format painter

and select A8 to T42
click on the format painter and then select the new sheet and click - so that the format is copied in

see sheet 3 - thats where I selected sheet1 A8 to T42 and format painter
and paste into sheet 3

you could also use copy > paste special> format

Its so complicated now - if you copy exactly the formulas and the applies to area , you may be OK
and then make sure they are in the same order - see the up/down arrows in the conditional format screen to move the rule

before using i would do a full test on this sheet - just to see if anything pops up as wrong, I would be surprised if it worked 100% for all possible conditions through out the sheet now

https://www.dropbox.com/s/gx07eyfv45qli15/Medical Physical ETAF_2 with Blue Colour Exp_etaf_v6.xlsx

 

[Taryn A]

That because I'm UK and reformatted
select the dates and then
format
custom
and put in type

MM/DD/YYYY

updated to US format in version 6 attached


I think this would need a macro to move the comments from a cell into the comments - not sure how to do that

added to the version 6 attached

done


you can copy to a new sheet
IF the columns are identical layout
then you should be able to use the format painter

and select A8 to T42
click on the format painter and then select the new sheet and click - so that the format is copied in

see sheet 3 - thats where I selected sheet1 A8 to T42 and format painter
and paste into sheet 3

you could also use copy > paste special> format

Its so complicated now - if you copy exactly the formulas and the applies to area , you may be OK
and then make sure they are in the same order - see the up/down arrows in the conditional format screen to move the rule

before using i would do a full test on this sheet - just to see if anything pops up as wrong, I would be surprised if it worked 100% for all possible conditions through out the sheet now

https://www.dropbox.com/s/gx07eyfv45qli15/Medical Physical ETAF_2 with Blue Colour Exp_etaf_v6.xlsx

Wow so you are in the UK, that's awesome! I wanted to check your response yesterday, but I'm so busy with other stuff at work! I take one step forward and 10 steps back lol! I haven't had a chance to look at the last spreadsheet you sent either, but I guess at this point I don't need to I'll just look at the one you gave me in this post version 6 right? Nor have I had a chance to thoroughly read this post, so I will do that and get back with you soon or at least that's my intentions. Thanks again!

 

[etaf]

but I guess at this point I don't need to I'll just look at the one you gave me in this post version 6 right?

yes, just look at version 6

 

[Taryn A]

yes, just look at version 6

I have a question, why do the Columns between each of the Sections (Columns D, I, N, & R) have text in them? Column D has "Column1", Column I has "Column2", Column N has "Column5", Column R "Column6"?
These Columns in between each of the Sections, should be blank as this is how my workbook is. Additionally, those columns in between are colored gray in my workbook. Do you think when I go to use the format painter that it will make a difference since those columns are coloured gray in my workbook? I'm not so worried about the colour, but if you are saying that I can use the format painter if all the rows/columns are the same I want to make sure that they are exactly the same, and those Columns in my workbook are blank. They are basically just separators between each of the Sections if that makes sense.

Also, Column L the Limited Column under the OSHA Section has "Limited3" and Column M has "Failed4"? Can we do away with those numbers? It should just read "Limited" and "Failed".

 

[etaf]

i made the whole section into a table
so that when you insert rows or add rows to the bottom of the table the formulas and also the conditional formatting copies into those rows.
a table wants to have titles in each column - you could just format the cell font to be white , so you cant see the title

or you can set up with no table , as you are copying across to another workbook, it probably wont be into a table and so you can ignore
you will just need to remember to extend all the conditions formats , so they appy to any new rows and also copy the formulas

Also, Column L the Limited Column under the OSHA Section has "Limited3" and Column M has "Failed4"? Can we do away with those numbers? It should just read "Limited" and "Failed".

this again will be due to setting up as a table , and so each title has to be unique , hence the numbers, but as you are copying to new work sheet - you can just ignore

 

[Taryn A]

Yeah I noticed that on sheet 3 of version 6 I was able to delete the text in those cells. However it wouldn't let me delete them in sheet 1. I'm sorry but I'm a bit confused about the table thing. I guess what I should be asking you is what way do you recommend that I copy the conditional formatting and formulas over the the workbook? should I use the format painter option? So you are saying that won't copy the table?