Hi all,
Happy Friday! I hope you are well.
I would like to highlight cells using conditional formatting once certain conditions are met. I am attaching a file for better understanding.
Essentially, this involves formatting so that when employees have used 50% or more of their annual leave entitlement, the cell fills in orange. When they have reached 75% or more, the cell fills in red.
D6 should be filled in orange, D7 should be filled in red.
Thanks
Please check the attached file and see if that works for you.
You don't need the second table, just the first one with the name, total entitlement and the days used.
Let us know if that works or any adjustments are required.
If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.
Thanks Oroos,
My bad. I had attached the wrong one.
I am attaching the correct one. Charles' figures should be in orange, Richard's figures should be in red, and Peter's ones in green (on summary tab).
Thanks, and apologies for the confusion.
Hi again,
Please refer to the attached file, conditional formatting is applied to the Summary sheet.
Note, I have added a column with the annual holiday entitlement. This makes the formulas much easier to compare the used days with the entitlement on the same sheet/row. The Annual Leave Entitlement sheet is not used for the formatting.
If it is not possible to have the annual entitlement on the summary sheet, we could hide the column... If this is a no-go either, we would have to resort to more complex formulae to lookup the value for each name and compare the values that way.
Let us know how we go with this one.
Thanks ORoos,
I have hidden the rows and the columns so they can't be seen. Is there any way to protect those columns and rows so they can't unhide them? I would only like to show the cells C5,C6, E5 and E6.
Regards
Hi again,
Ok, so if you only want the 2 header rows and one dropdown box to select a name, we can do that too.
Please refer to the attached file.
Select the name in cell C6.
The conditional formatting will retrieve the annual entitlements from the other sheet and work out the percentage mark of the used leave days.
Formula for 50% (orange):Formula:
Please Login or Register to view this content.
IF you add more names, you will need to adjust the reference range [..$C$5:$D$7..] in the orange and red conditional formatting formulas.
Trust this helps
Hi ORoos,
Again, many thanks for your help.
"IF you add more names, you will need to adjust the reference range [..$C$5:$D$7..] in the orange and red conditional formatting formulas."
What if I give a name to the range C4:D7 on the sheet Annual leave entitlement so the conditional formatting reference range doesn´t have to be adjusted? I am saying this because the file will be shared with other users so I wouldn´t like them to have to amend anything.
Thanks!
Yes we can use a named range to make the dropdown box dynamic.
I used the following formula:Formula:Please Login or Register to view this content.
Note: the list of names must be continuous, otherwise you get blank spaces in your dropdown list.
Cheers
Hi ORoos,
Thanks for your help.
However, I was referring to the conditional formatting formula not to the dropdown menu for the employee's names.
I have added another employee (Mark) but the conditional formatting doesn't work as it's not dynamic. Would it be possible to make it dynamic so the reference range $C$5:$D$7 doesn't have to be manually adjusted, please?
Thanks!
Just make the range longer than it will ever need to be, e.g. =$D6>VLOOKUP($C$6,'Annual leave entitlement'!$C$5:$D$700,2,FALSE)*0.75
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
As per AliGW, you could just increase the range. Probably the easiest solution.
If you want a dynamic solution, we can use the offset function. Attached file is updated with this formula;
Formula:Please Login or Register to view this content.
I would recommend NOT using the volatile OFFSET unless it is really, really, really necessary!
Many thanks for your help!
Thanks, I am going to use AliGW's approach as it seems it's not a good idea to use volatile offset. However, much appreciated your help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks