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]

I discovered one issue. In that LET formula, change the =2 to >=2 in all places, like this:

LET(scds,$A$2:$A$10,rday,M3:M14,dfltr,FILTER(scds,IF(COUNTIF(rday,"D")>=2,scds<>"D"),scds),efltr,FILTER(dfltr,IF(COUNTIF(rday,"E")>=2,dfltr<>"E"),dfltr),nfltr,FILTER(efltr,IF(COUNTIF(rday,"N")>=2,efltr<>"N"),efltr),nfltr)

The issue is that if someone attempts to manually enter a disallowed D, N, or E entry (so that 3 would exist in the column), the LET formula quickly recomputes, determines that 3 is not equal to 2, therefore the previously disallowed shift code is now allowed. This change prevents that work-around. If you'd prefer, the Data Validation can display an error message, such as "Only 2 D, N, E entries allowed" if an error is encountered. After you get the worksheet working, let me know if you want the filtered list of shift codes to be displayed in any particular order (alphabetical?). That can be done by wrapping the output of the LET function with a SORT function.

 

[sandyandy5]

Oops...I noticed my suggestion to use nested SUBSTITUTE functions has a problem, as it strips the "D" from all shift codes containing a D, not just the single element "D". But that's okay because you're using 365, so I'd suggest a different approach to filter the valid shift code list using nested FILTER functions. This has the added benefit of automatically closing the blanks in the filtered list. To trim down the formula, I've placed the three steps for filtering into a single LET function, which first performs a "dfltr" (D filter)...which looks at the roster day schedule and counts whether 2 "D" entries are present. If so, then "D" is removed from the list before passing the list along to the "efltr" where "E" entries are counted, and "E" is removed should 2 already exist. Then the resulting list is passed to the "nfltr" to count "N" entries and remove "N" as an option should 2 already exist. The result of this triple check is the "nfltr" array, which spills down the helper column. The Data Validation for that same day on the Roster sheet then references this spilling array. So for a 28 day Roster, you'll need 28 helper columns populated with the formula shown in G3 and copied across all 28 columns. Before pulling the formula across, first confirm that the two cell ranges in this formula are correct:
The Initial list of Valid Shift Codes is... scds,$A$2:$A$10
The first day on the roster schedule table where entries are to be made is...rday,M3:M14
Note that the first of these has fixed references (the $ signs that lock the column and row locations), while the second of these has a relative reference (because this range needs to change when the formula is copied across the top row of the helper block...so M3:M14 refers to the 1st day on the fillable roster table, and N3:N14 would refer to the 2nd day on the roster table, etc.). Since your roster table is on a different sheet, this reference to the roster day ("rday") will look something like...rday, Roster!D4:D16...in the LET function.
After confirming that the first day of the roster table performs as expected, you should be able to populate the helper block by copying the first formula across the top row of the helper section. Then in your roster table, select the first day's entire fillable region (in your example above, I believe that is D4:D16) and go to Data Validation>allow a List, then in the Source field, enter =Valid!G$3# (in this example that's the reference to the first day's filtered list). Note that the sheet is referenced, and the top cell of the first day's spilling helper array is referenced, but only the row 3 is fixed (the $3). This reference uses the hash # afterward to indicate that you want the entire spilling array returned. The reason for not fixing the column G in this Source field reference is to allow it to change as the Data Validation is copied across to the other days in the roster table. To do that, select the first day's entire fillable region then Ctrl-C to copy to the clipboard, then select the entire range of the fillable month/4-week roster table and execute Home>Paste> Paste Special > Validation, which copies only the validation into the other fillable roster cells. If done correctly, the data validation reference for the 2nd day should have automatically updated to refer to the 2nd column in the helper table, and so on.
I should have mentioned...the reason for this somewhat convoluted approach is that Excel's Data Validation does not accept more complex array formulas. It will, however, accept a reference to a spilling array created by such a formula.

MrExcel_20220524.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Valid Shift Codes	Day Shift Codes	Night Shift Codes	EFT Codes	07:00 - 16:30		Reduction of D,E,N Choices Based on Counts from Roster (Filtered Unique for Each Day)						Notional Scheduling Days on Roster Sheet
2	D	D	N	N	D		7/4	7/5	7/6	7/7			7/4	7/5	7/6	7/7
3	MW	DSN	CN	CN	CD		MW	D	MW	D			Off
4	E	E	NSN	NSN	DSS		Off	MW	E	MW			MW		N
5	N	MW		ADO			MD	N	Off	E
6	Off			DSS			ADO	Off	MD	N			N		D
7	MD			ESS			L	MD	ADO	Off				E
8	ADO			MD			PD	ADO	L	MD			D		N
9	L			NSS				L	PD	ADO			N
10	PD			Nuts				PD		L			E	E
11										PD			L
12													E		D
13													D
14
Valid

Cell Formulas
Range	Formula
G2	G2	=Roster!D2
H2:J2,N2:P2	H2	=G2+1
G3:G8,J3:J11,I3:I9,H3:H10	G3	=LET(scds,$A$2:$A$10,rday,M3:M14,dfltr,FILTER(scds,IF(COUNTIF(rday,"D")=2,scds<>"D"),scds),efltr,FILTER(dfltr,IF(COUNTIF(rday,"E")=2,dfltr<>"E"),dfltr),nfltr,FILTER(efltr,IF(COUNTIF(rday,"N")=2,efltr<>"N"),efltr),nfltr)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
M3:P14	List	=G$3#

Fabulous work! Ill give it a go today while the kids are at kinder for a few hours.
Would you recommend putting the helper columns on the same 'roster' page or put it with the 'valid' page?
thankyou!

 

[KRice]

I would keep the helper columns on either the Valid sheet or an entirely different sheet, and then perhaps hide that sheet (right click on sheet name tab and select Hide). Both of those options reduce the risk that the formulas get overwritten accidentally, although you may want to make a brief note somewhere about those details, as you may need to periodically update the formulas if the ranges or criteria change. Let me know if you encounter any difficulties. I can upload a link to a working version of the sheets if necessary, although it would be better if you can integrate the formulas into your workbook as mine has none of the extensive conditional formatting that you've implemented.

 

[sandyandy5]

It works!!! Kirk you are a CHAMPION!! For something I've been pulling my hair out for days you've fixed and it works and is user friendly!! Thankyou so so much for all of your time and effort to finding a solution to this problem! Your concise instructions and explanations of why you were entering the formula in certain ways has definitely helped this succeed!!
Thankyou so much again.
Kindest regards
Andrea

Roster Template.xlsx
	B	C	D	E	F	G
1	04 July 2022 -	EFT	M	T	W	T
2	31 July 2022		4	5	6	7
3	J PITSON	1.00
4	S COLLIER-NDx2/fortnight	0.74
5	D CUSHING	1.00	n
6	H FITT- ND 1st fortnight	0.63	d
7	J FORBES- NDx2/fortnight	0.74	e
8	H HINSON- ND 2nd fortnight	0.74	n
9	S HOCKING	0.21	d
10	D KERR- ND for last 3wks	1.00	off
11	K MCCORMICK- NDx1 in last wk	0.63	l
12	S NEWNHAM- NDx1 in last wk	0.42	pd
13	B SALATHIEL	0.74	e
14	T VIERBOOM- NDx4 1st fortnight	1.00
15	M WELCH- ND 1st fortnight	0.32
16	M YOUNG	0.63
Roster

Cell Formulas
Range	Formula
B2	B2	=B1+27
D2	D2	=B1
E2:G2	E2	=D2+1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D15:AE15	Cell Value	contains ""	text	NO
D15:AE15	Cell Value	contains ""	text	NO
D15:AE15	Cell Value	contains ""	text	NO
D15:AE15	Cell Value	contains ""	text	NO
D15:AE15	Cell Value	contains ""	text	NO
D15:AE15	Cell Value	contains ""	text	NO
D13:AE13	Cell Value	contains ""	text	NO
D13:AE13	Cell Value	contains ""	text	NO
D13:AE13	Cell Value	contains ""	text	NO
D13:AE13	Cell Value	contains ""	text	NO
D13:AE13	Cell Value	contains ""	text	NO
D13:AE13	Cell Value	contains ""	text	NO
D11:AE11	Cell Value	contains ""	text	NO
D11:AE11	Cell Value	contains ""	text	NO
D11:AE11	Cell Value	contains ""	text	NO
D11:AE11	Cell Value	contains ""	text	NO
D11:AE11	Cell Value	contains ""	text	NO
D11:AE11	Cell Value	contains ""	text	NO
D3:W3	Cell Value	contains ""	text	NO
D3:W3	Cell Value	contains ""	text	NO
D3:W3	Cell Value	contains ""	text	NO
D16:AE16	Cell Value	contains ""	text	NO
D16:AE16	Cell Value	contains ""	text	NO
D16:AE16	Cell Value	contains ""	text	NO
D14:AE14	Cell Value	contains ""	text	NO
D14:AE14	Cell Value	contains ""	text	NO
D14:AE14	Cell Value	contains ""	text	NO
D12:AE12	Cell Value	contains ""	text	NO
D12:AE12	Cell Value	contains ""	text	NO
D12:AE12	Cell Value	contains ""	text	NO
D12:AE12,D16:AE16,D4:AE10	Cell Value	contains ""	text	NO
D12:AE12,D16:AE16,D4:AE10	Cell Value	contains ""	text	NO
D12:AE12,D16:AE16,D4:AE10	Cell Value	contains ""	text	NO
U36:Y36,AB36:AE36,K36:L36,V28:AE28,D26:AC26,D20:Q20,I27:AE27,K35:AE35,Y20:AC20,I30:J30,AD122:AE126,W122:X126,P122:Q126,I122:J126,I118:J120,P118:Q120,W118:X120,AD118:AE120,D22:AE22,D32:AE34,Y26:AC28,D27:Q28,D114:AE117,I18:J18,K37:AE38,P36:Q37,AD35:AE37	Cell Value	contains ""	text	NO
U36:Y36,AB36:AE36,K36:L36,V28:AE28,D26:AC26,D20:Q20,I27:AE27,K35:AE35,Y20:AC20,I30:J30,AD122:AE126,W122:X126,P122:Q126,I122:J126,I118:J120,P118:Q120,W118:X120,AD118:AE120,D22:AE22,D32:AE34,Y26:AC28,D27:Q28,D114:AE117,I18:J18,K37:AE38,P36:Q37,AD35:AE37	Cell Value	contains ""	text	NO
U36:Y36,AB36:AE36,K36:L36,V28:AE28,D26:AC26,D20:Q20,I27:AE27,K35:AE35,Y20:AC20,I30:J30,AD122:AE126,W122:X126,P122:Q126,I122:J126,I118:J120,P118:Q120,W118:X120,AD118:AE120,D22:AE22,D32:AE34,Y26:AC28,D27:Q28,D114:AE117,I18:J18,K37:AE38,P36:Q37,AD35:AE37	Cell Value	contains ""	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B1	Any value
D3:AE3	List	=Valid!$A$2:$A$17
D4:D16	List	=Valid!M$3#
E4:AE16	List	=Valid!N$3#

 

[KRice]

That's great news, Andrea. I'm happy to help. I meant to ask earlier, did you see the tweak to the formula I mentioned in post #11, where the =2 should be >=2 in three places? I discovered the issue when I tried to manually enter (bypassing the dropdown) a prohibited shift code...and was surprised when it was accepted. Then I saw why...the >=2 resolves that problem. Also, if you'd like the dropdown list to appear in some order, that can probably be added to the LET formula.

 

[sandyandy5]

That's great news, Andrea. I'm happy to help. I meant to ask earlier, did you see the tweak to the formula I mentioned in post #11, where the =2 should be >=2 in three places? I discovered the issue when I tried to manually enter (bypassing the dropdown) a prohibited shift code...and was surprised when it was accepted. Then I saw why...the >=2 resolves that problem. Also, if you'd like the dropdown list to appear in some order, that can probably be added to the LET formula.

I missed info from the #11 post. The way i got around it was increasing it to one number higher that i needed throughout the different staff pools throughout the roster. see below
=LET(scds,$A$2:$A$18,rday,Roster!D80:D83,dfltr,FILTER(scds,IF(COUNTIF(rday,"D")=3,scds<>"D"),scds),efltr,FILTER(dfltr,IF(COUNTIF(rday,"E")=3,dfltr<>"E"),dfltr),nfltr,FILTER(efltr,IF(COUNTIF(rday,"N")=3,efltr<>"N"),efltr),nfltr)
I would have never of been able to fix the issue without your help!

 

[KRice]

If you leave it with =3, then users will be able to select a D, E, or N up to 3 times before the shift code disappears from the dropdown list...but they will still be able to manually add another one (a 4th one), and because 4<>3, the disallowed code will then reappear in the drop down list, which will allow a 5th, 6th, etc. entry of the same shift code using either the drop down list or manual entry. I would suggest trying that out, and after confirming, you will probably want to go with >=2 instead, which will enforce the desired data validation rule for either drop down list removal or denial upon attempting to manually enter.

If you have multiple sets of the formula, each with different ranges, you would have two options for making the edits. One is manually...I think you really would need to change the first formula of each set and then copy across the columns.. That's a nice feature of the spilling arrays...no need to drag them down. The other option is to confirm that you don't have "=3" anywhere else on the sheet in any other formulas. If you do not, then you could use Home > Find & Select > Replace, and then enter =3 and >=2 in the find and replace fields, and then confirm that the action will occur only on the current sheet.

 

[sandyandy5]

If you leave it with =3, then users will be able to select a D, E, or N up to 3 times before the shift code disappears from the dropdown list...but they will still be able to manually add another one (a 4th one), and because 4<>3, the disallowed code will then reappear in the drop down list, which will allow a 5th, 6th, etc. entry of the same shift code using either the drop down list or manual entry. I would suggest trying that out, and after confirming, you will probably want to go with >=2 instead, which will enforce the desired data validation rule for either drop down list removal or denial upon attempting to manually enter.

I've done the first two weeks worth of testing and it currently appears to be working but ill change it to the >=2 code instead as I don't want it to fail. Thankyou for the constructive feedback

 

[KRice]

Here is a screenshot of my test:

The helper column formula that displays current valid shift codes for 7/4 uses =3, so I was able to add the first three "D" entries in the roster table using the drop down feature. Then as expected, after entering the 3rd D, the helper formula removed D from its list and it was not available when I tried to use the drop down feature to add a 4th D to the roster under 7/4...so I manually typed D and hit enter, and because 4 does not equal 3, the formula in the helper column evaluates this as TRUE and returns the previous list, which had a D in it...not desirable! Then, with the D available in the drop down list, I was able to use the drop down feature like normal to add a 5th, 6th, and 7th D.

The helper column formula that displays current valid shift codes for 7/5 uses >=2, so I was able to add the first two "E" entries in the roster table using the drop down feature, at which point the helper formula removed E from its list and it was not available when I tried to use the drop down feature to add a 3rd E to the roster table. Here I am showing the outcome after I attempted to bypass the constraint by manually typing E and hitting enter. The error message I set up under Data Validation kicks in and explains why this is not a valid entry, and then I need to clear the message by either clicking on Retry or Cancel, both of which clear the pending entry of E from the roster table, and then I have another chance to enter a valid shift code. Even if a specialized error message is not set up, a generic error message explaining the entry is invalid would be displayed. This is why the criteria should always be looking at the threshold and beyond to prevent a manual work-around, whether intentional or inadvertent, from bypassing the constraint.

 

[sandyandy5]

I've also found another issue while at work lastnight. As the work system is using 2013 excel, when i opened it up none of the formulas worked and staff were even able to enter anything typed without getting an error msg within the cells. What have a dont wrong here or is it the systems not able to work across each other?