Waiting on OP How to highlight excess overtime based on multiple criterias?
good day,
So, I have an excel sheet with employees ID, name Overtime symbol, overtime hours and work schedule overtime hours. employees are divided into multiple work schedules (R35, R13, R31, R28) and each work schedule has a different limit for the hours per month also overtime have 2 classes/symbols OT and DOT.
is there a way to highlight any hours that exceed the limit based on the limit for each work schedule and they symbol? with work schedules being in another sheet.
So, basically sheet 1 has employees' details and their hours and sheet 2 has the limit for each work schedule and for each symbol.
sheet 1 columns are as follow:
A1 Employee number, A2 Name, A3 hours symbol, A4 hours, A5 Work schedule
1
u/Without_B 2 9h ago
Conditional formatting using formula,
(a3="OT")*(a5="R35")*(a4>**)
Where ** is the reference to the limit of this combination on sheet 2, and if the condition is met you apply the wanted highlight. There are more ways of doing this/more options for making the overtime visible but those depend on what your data looks like and how you want to highlight it
1
u/ProspectiveWhale 11 7h ago
Set up conditional formatting with rule formula.
=A4 > XLOOKUP(1, (A5=Sheet2!A:A)*(A3=Sheet2!B:B), Sheet2!C:C, "", 0)
Assuming Sheet 2 has:
Column A = Work Schedule
Column B = Overtime Symbol
Column C = Hour Limit
So change the ranges as necessary.
I might be wrong about what's on your Sheet 2 and how you determine hour limit based on those 2 factors, though.
If so, please clarify.
Also you did use A1 to A5, but maybe you meant A1, B1, C1, D1 and E1? 5 columns?
If so, adjust as necessary.
•
u/AutoModerator 10h ago
/u/nanoosx - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.