Data validation, formula difficulties

[MadBern]

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:

1. Employee name must be found in column E
2. Employee must have set status as "Online" in column F
3. Number of emps. on break must not have been reached for one or more of the competencies that the booker has

Now, I got the first two conditions to work very easily, but I'm struggling with the third.

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

 

[etaf]

so this going to be a real time spreadsheet that employees fill in
so is the plan that they want a break and so can goto the spreadsheet to see if thats possible
how long is a break ?
how do they fill in a cell to say they are about to take a break or look to see if they can take a break

Is this something they will do when working from home ?

Emp2 has comp 2 & 4

4 people can be off for comp2
3 people off for comp4

so if he/she takes a break , that will count for each comp and so if 0 limit for comp2 but 3 out already for comp4 then cannot take break

 

[MadBern]

so this going to be a real time spreadsheet that employees fill in
so is the plan that they want a break and so can goto the spreadsheet to see if thats possible
how long is a break ?
how do they fill in a cell to say they are about to take a break or look to see if they can take a break

Is this something they will do when working from home ?

Emp2 has comp 2 & 4

4 people can be off for comp2
3 people off for comp4

so if he/she takes a break , that will count for each comp and so if 0 limit for comp2 but 3 out already for comp4 then cannot take break

That's right, the idea is it being a real time sheet which tells them if a break is possible.
The lenght of the break doesn't matter, I think. I thought about activating a timer when a slot is filled in, but for that I need VBA, and macros doesn't function in Excel Online (we use Office365).
They book a break just by writing their name in a vacant slot and when they return from break they have to clear the cell again so that another emp can book (if all other slots are filled).

Yes, this is for home office-workers.

That's right. In the case of emp2, if there already are three workers on break with comp4 and zero with comp2, then emp2 cannot book a break until at least one comp4 employee is back.

 

[MadBern]

That's right, the idea is it being a real time sheet which tells them if a break is possible.
The lenght of the break doesn't matter, I think. I thought about activating a timer when a slot is filled in, but for that I need VBA, and macros doesn't function in Excel Online (we use Office365).
They book a break just by writing their name in a vacant slot and when they return from break they have to clear the cell again so that another emp can book (if all other slots are filled).

Yes, this is for home office-workers.

That's right. In the case of emp2, if there already are three workers on break with comp4 and zero with comp2, then emp2 cannot book a break until at least one comp4 employee is back.

I think I found one problem though, but the solution eludes me.
The formula I have in the Data validation for S9 (Slot 1) at the moment is: =AND(COUNTIFS($E:$E,S9,$F:$F,"Online",M:M,"<2")=1) and it doesn't accept me typing "Emp. 1" in S2 now because the values in row M are a result of a formula. If I manually change the value in M2 to a "1", then the validation works perfectly and I can type "Emp.1" in S2 without problems.
So its the formula in the cells in rows M-Q that's keeping it from functioning? Any way around that?


Mads

 

[etaf]

They book a break just by writing their name in a vacant slot and when they return from break they have to clear the cell again

is a break them trying to change status to offline from online, dont quite understand they enter their name ,
would it not be easier to have all the details preformatted and then a dropdown to say - take a break , then it will say YES or NO
OR
do you meant the cells Labeled SLOT
I assume you will never have more than 5 people off at one time , given that you have slot 1-5

OK, so now i know you have to fill in the SLOT section with a name i can see it working a bit better

So in the data Validation I have added
=AND(COUNTIFS($E:$E,S9,$F:$F,"Online"),S2<=B2)
which will work for comp 1 just need to look for all comp

 

[etaf]

i have put this into SLOT 1 data validation
=AND(COUNTIFS($E:$E,S9,$F:$F,"Online"),S2<=B2,V2 <= B3, Y2 <= B4, AB2 <= B5, AE2 <=B6)
so we are also checking that all the comp are below or = to the limits
Seems to work, but have not extensively tested or modified the other SLOT
Does that WORK for slot 1 OK, if so then a simple copy and paste for the other 4 SLOT data validation

Dropbox - File Deleted - Simplify your life

www.dropbox.com

you may also want to look at A13
Counting the number of comp that are in a SLOT and also ONLINE

This could be difficult to manage , if someone doesn't bother to clear their name in a slot
OR 2 people try and update at exactly the same time, not sure how google docs manages that
just some thoughts
How many people accessing this
and would a break just be going to the kitchen and making a cup of coffee and so be away from the screen for 10-15mins or a toilet break

 

[MadBern]

i have put this into SLOT 1 data validation
=AND(COUNTIFS($E:$E,S9,$F:$F,"Online"),S2<=B2,V2 <= B3, Y2 <= B4, AB2 <= B5, AE2 <=B6)
so we are also checking that all the comp are below or = to the limits
Seems to work, but have not extensively tested or modified the other SLOT
Does that WORK for slot 1 OK, if so then a simple copy and paste for the other 4 SLOT data validation

Dropbox - File Deleted - Simplify your life

www.dropbox.com

=AND(COUNTIFS($E:$E,S9,$F:$F,"Online"),S2<=B2,V2 <= B3, Y2 <= B4, AB2 <= B5, AE2 <=B6) - I've already tried that formula. The problem then is that Emp.1, who doesn't have comp4, is denied booking a break if comp4 has reached its limit, but comp1, which Emp.1 has, is not at its limit.
So it doesn't take into account which comps the employee has and doesn't have.

A13 is blank, as far as I can see. What do you want me to look at?

Number of people accessing would be about 25 - 30, tops. And ideally it will be used every time someone is away from their desk, but mostly for 10 - 15 minute breaks, I should think.

And don't worry. If someone doesn't clear the cell they've used, the other emps will let them know


Mads

 

[etaf]

i thought that was OK , because those values dont change, although comp4 does not seem to work on your count

 

[MadBern]

i thought that was OK , because those values dont change, although comp4 does not seem to work on your count

I don't know why the count doesn't work for comp4 in the document online, it works fine in the local version I'm working in.

 

[etaf]

have a look at the counts i added in column AI to AM3
its using a count
and is different to your counts
And the situation you describe does not happen for my data validation
update of excel - NOT using the online version at the moment

Dropbox - File Deleted - Simplify your life

www.dropbox.com