Cell validation with limits and multiple formulas

[sandyandy5]

Hi! First time poster disclaimer to start with!
I’m creating a nursing shift roster and wanting to limit only 2 ‘D’ shifts per day then to lock others out. I dont know what formula I should be putting into the custom cell validation to achieve this. My current formula is =SUMPRODUCT(COUNTIF(D$4:D$16, Vailid!$E$2:$E$168)).
Any help or ideas of how to make this work would be greatly appreciated!cheers

 

[KRice]

That's great. I'm not sure that I follow your comment about the keyboard. Are you saying that if there is, say one D left for a day, you can select the D from the dropdown with the mouse, but what?...you get an error if you manually type a D, or you try to navigate the dropdown with the keyboard?

 

[sandyandy5]

That's great. I'm not sure that I follow your comment about the keyboard. Are you saying that if there is, say one D left for a day, you can select the D from the dropdown with the mouse, but what?...you get an error if you manually type a D, or you try to navigate the dropdown with the keyboard?

Yes exactly what happens an error if you manually type it even if there is one 'd' left

 

[KRice]

I can't duplicate that. Can you confirm that the cell where you attempt to enter the "d", what does the Data Validation source formula look like? Is it pointing to the correct column in your dropdown list helper table, and have the ranges in the OFFSET formula adjusted correctly? I'm assuming that if you jump over to the helper table, you still see a "d" in the table?

 

[KRice]

Oh...I think I see the issue. Go back to your helper table and examine the formula: When you get to this part...
SMALL(IF((IF(NOT(IF(COUNTIF(Roster!D4:$D$16,"D")
You are missing a $ sign in front of the D4, so that array was walking away from you. It should read D$4. In fact, I think you have most of those ranges fixed but they should be semi-fixed. Compare to my original formula:
=IFERROR(INDEX(IF(NOT(IF(COUNTIF(Q$3:Q$14,"D")>=2,($A$2:$A$10="D"))+IF(COUNTIF(Q$3:Q$14,"E")>=2,($A$2:$A$10="E"))+IF(COUNTIF(Q$3:Q$14,"N")>=2,($A$2:$A$10="N"))),$A$2:$A$10,""),SMALL(IF((IF(NOT(IF(COUNTIF(Q$3:Q$14,"D")>=2,($A$2:$A$10="D"))+IF(COUNTIF(Q$3:Q$14,"E")>=2,($A$2:$A$10="E"))+IF(COUNTIF(Q$3:Q$14,"N")>=2,($A$2:$A$10="N"))),$A$2:$A$10,"")<>""),ROW(INDIRECT("1:"&COUNTA($A$2:$A$10))),""),ROW($A1))),"")

Mine refer to Q$3:Q$14 and not $Q$3:$Q$14.

 

[sandyandy5]

I can't duplicate that. Can you confirm that the cell where you attempt to enter the "d", what does the Data Validation source formula look like? Is it pointing to the correct column in your dropdown list helper table, and have the ranges in the OFFSET formula adjusted correctly? I'm assuming that if you jump over to the helper table, you still see a "d" in the table?

So the data validation source formula is =OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1). What would happen if we changed the last number in this formula from 1 to a 2? would that solve it?

When i highlight the cell within the column with either the mouse or by using the arrows on the keyboard, the dropdown arrow is into the next box to the right. once selecting a shift from here it is put into the highlighted cell not the one on the right of it.
When looking at the helper table there is one remaining 'D' there when trying to enter the shift with the keyboard.

 

[sandyandy5]

Oh...I think I see the issue. Go back to your helper table and examine the formula: When you get to this part...
SMALL(IF((IF(NOT(IF(COUNTIF(Roster!D4:$D$16,"D")
You are missing $ signs in front of the D4, so that array was walking away from you. It should read $D$4.

mmm it doesn't like it. Error message coming up stating 'the formula it doesn't match the data validation for this cell'

 

[KRice]

I just revised post 44, so read it again, please...I believe that is where the problem lies. Can you copy and paste again (sorry) the formula in the upper left of your helper table.

 

[sandyandy5]

I just revised post 44, so read it again, please...I believe that is where the problem lies. Can you copy and paste again (sorry) the formula in the upper left of your helper table.

All good, revising now

 

[sandyandy5]

Oh...I think I see the issue. Go back to your helper table and examine the formula: When you get to this part...
SMALL(IF((IF(NOT(IF(COUNTIF(Roster!D4:$D$16,"D")
You are missing a $ sign in front of the D4, so that array was walking away from you. It should read D$4. In fact, I think you have most of those ranges fixed but they should be semi-fixed. Compare to my original formula:
=IFERROR(INDEX(IF(NOT(IF(COUNTIF(Q$3:Q$14,"D")>=2,($A$2:$A$10="D"))+IF(COUNTIF(Q$3:Q$14,"E")>=2,($A$2:$A$10="E"))+IF(COUNTIF(Q$3:Q$14,"N")>=2,($A$2:$A$10="N"))),$A$2:$A$10,""),SMALL(IF((IF(NOT(IF(COUNTIF(Q$3:Q$14,"D")>=2,($A$2:$A$10="D"))+IF(COUNTIF(Q$3:Q$14,"E")>=2,($A$2:$A$10="E"))+IF(COUNTIF(Q$3:Q$14,"N")>=2,($A$2:$A$10="N"))),$A$2:$A$10,"")<>""),ROW(INDIRECT("1:"&COUNTA($A$2:$A$10))),""),ROW($A1))),"")

Mine refer to Q$3:Q$14 and not $Q$3:$Q$14.

Revised though the keyboard error persists in the roster sheet. I think its workable for staff to just use the mouse instead of the keyboard though. I'm just glad all the formulas will work and allow input! lol

Roster Template.xlsx
	L	M	N
1		ANUM helper comumns
2		1	2
3		D
4		E
5		N
6		OFF
7		MD
8		ADO
9		L
10		PD
11		SD
12		OW
13		BOS
14		DSN
15		ESN
16		NSN
17		MWSN
18		MHC
19		MW
Valid

Cell Formulas
Range	Formula
M3:M19	M3	=IFERROR(INDEX(IF(NOT(IF(COUNTIF(Roster!D$4:D$16,"D")>=2,($A$2:$A$18="D"))+IF(COUNTIF(Roster!D$4:D$16,"E")>=2,($A$2:$A$18="E"))+IF(COUNTIF(Roster!D$4:D$16,"N")>=2,($A$2:$A$18="N"))),$A$2:$A$18,""),SMALL(IF((IF(NOT(IF(COUNTIF(Roster!D$4:D$16,"D")>=2,($A$2:$A$18="D"))+IF(COUNTIF(Roster!D$4:D$16,"E")>=2,($A$2:$A$18="E"))+IF(COUNTIF(Roster!D$4:D$16,"N")>=2,($A$2:$A$18="N"))),$A$2:$A$18,"")<>""),ROW(INDIRECT("1:"&COUNTA($A$2:$A$18))),""),ROW($A1))),"")

 

[KRice]

After fixing the helper table formula, did you copy that formula across the top of the table and then pull everything down to the bottom so that the revised formula is populating the entire table? I've examined the last formula you posted and do not see any issues:

=IFERROR( INDEX(IF(NOT(
      IF( COUNTIF(Roster!D$4:D$16,"D") >=2,  ($A$2:$A$18="D") )  +
      IF( COUNTIF(Roster!D$4:D$16,"E")  >=2,  ($A$2:$A$18="E") )  +
      IF( COUNTIF(Roster!D$4:D$16,"N") >=2,  ($A$2:$A$18="N") )),
                                                                               $A$2:$A$18,""),       
  SMALL(IF((IF(NOT(
      IF( COUNTIF(Roster!D$4:D$16,"D") >=2,  ($A$2:$A$18="D") )  +
      IF( COUNTIF(Roster!D$4:D$16,"E") >=2,  ($A$2:$A$18="E") )   +
      IF( COUNTIF(Roster!D$4:D$16,"N") >=2,  ($A$2:$A$18="N") )),
                                                                              $A$2:$A$18,"")<>""), ROW(INDIRECT("1:"&COUNTA($A$2:$A$18))),""),ROW($A1))),"")

So your initial shift codes are in $A$2:$A$18, and the first day of interest in the roster table is Roster!D$4:D$16 (so staff to be scheduled are on those same rows). I'm curious what the answer to my question above is, because if you did, the only other place to look is the data validation formula.