Hello again, helpful Excel-masters
Because of this Corona-thing going around all of our employees have been asked to work form home for the foreseeable future.
One issue we have is that we have to limit how many employees are on break at any given time, which is easily done when they're all in the same office space, but troublesome now when they're not.
So I've been cooking up an Excel-sheet where they can book breaks, but I need to some Data validation to set certain limits. (I have to use Data validation as VBA/macros apparently aren't usable in Excel Online).
Link to file here: Breaks.xlsx
These are the terms that have to apply to be able to book one of the five slots:
If the employee has a certain competency, he/she is assigned a "1" in columns G - K.
The numbers in columns M - Q was my attempt to give a reference to the Data validation where it returns a "0" if the emp. doesn't have the competency and the break limit hasn't been reached. It returs a "1" if one of the conditions are met and a "2" if both conditions are met.
So I tried to get the validation to find the "2"s, but it didn't work how I hoped. If one of them had a "1" it would accept the booking even if another had a "2". For all i know, the entire table in M - Q isn't needed at all...
I've tried AND-formulas, OR-formulas, COUNTIF, COUNTIFS and VLOOPUP in different combinations, but no luck. I also tried simpler stuff, like S2 < B2 without results. I must be missing something.
Anyone here who has a clue?
Regards,
Mads
Because of this Corona-thing going around all of our employees have been asked to work form home for the foreseeable future.
One issue we have is that we have to limit how many employees are on break at any given time, which is easily done when they're all in the same office space, but troublesome now when they're not.
So I've been cooking up an Excel-sheet where they can book breaks, but I need to some Data validation to set certain limits. (I have to use Data validation as VBA/macros apparently aren't usable in Excel Online).
Link to file here: Breaks.xlsx
These are the terms that have to apply to be able to book one of the five slots:
- Employee name must be found in column E
- Employee must have set status as "Online" in column F
- Number of emps. on break must not have been reached for one or more of the competencies that the booker has
If the employee has a certain competency, he/she is assigned a "1" in columns G - K.
The numbers in columns M - Q was my attempt to give a reference to the Data validation where it returns a "0" if the emp. doesn't have the competency and the break limit hasn't been reached. It returs a "1" if one of the conditions are met and a "2" if both conditions are met.
So I tried to get the validation to find the "2"s, but it didn't work how I hoped. If one of them had a "1" it would accept the booking even if another had a "2". For all i know, the entire table in M - Q isn't needed at all...
I've tried AND-formulas, OR-formulas, COUNTIF, COUNTIFS and VLOOPUP in different combinations, but no luck. I also tried simpler stuff, like S2 < B2 without results. I must be missing something.
Anyone here who has a clue?
Regards,
Mads
