Cell validation with limits and multiple formulas

sandyandy5

New Member
Joined
May 24, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Roster Template.xlsx
ABCDEFG
1Maternity ward04 July 2022 -EFTMTWT
231 July 20224567
3Leadership J PITSON1.00
4S COLLIER-NDx2/fortnight0.74
5D CUSHING1.00
6H FITT- ND 1st fortnight0.63
7J FORBES- NDx2/fortnight0.74
8H HINSON- ND 2nd fortnight0.74
9S HOCKING0.21
10D KERR- ND for last 3wks1.00
11K MCCORMICK- NDx1 in last wk0.63
12S NEWNHAM- NDx1 in last wk0.42
13B SALATHIEL0.74
14T VIERBOOM- NDx4 1st fortnight1.00
15M WELCH- ND 1st fortnight0.32
16M YOUNG0.63
17T BOSMA0.63
18E CARRASCO0.63
19RMM AKERS0.32
20A ANGEL0.84
21A BAILEY0.63
22R BARKER0.63
23M BUCCIOL0.84
24E CHEUNG0.84
25M CHITTICK- ND both fortnights1.00
26K COLLINS0.63
27K CROWE0.53
28M DAVIS0.42
29K DICKINS0.42
30M FITZGERALD0.84
31C FLETCHER0.95
32D FORD0.21
33H GAMBLE0.53
34N GARLAND0.53
35A HARRISON0.21
36S HARTNEY- NDx2 in 1st fortnight0.84
37S HAY0.84
38E HEALY0.21
39S JORGENSON0.84
40M KEKICH0.84
41C LISTER0.53
42K MACMUNN0.63
43E MATUSCHKA0.42
44E MCGOVAN0.42
45I MCKINNON0.84
46S MCINTOSH0.42
47B MILLER0.84
48B POULTER0.63
49C RANDALL0.21
50J REEVES0.74
51S RISSTROM0.63
52F ROBB0.21
53N ROBERTS0.63
54M ROWE0.84
55C SAVAGE0.21
56V SAVAGE0.84
57K SCHEMBRI0.32
58A SCOTT0.32
59D SMITH0.95
60C SLATTERIE-SMITH0.74
61Z SURGEONER0.63
62J TURNBULL0.42
63A VALENTINE0.84
64M VAN WINGERDEN0.53
65M VIERGEVER0.42
66K WALLIS0.95
67I WATERSON0.53
68L WILLIAMS0.21
69D WILSON0.42
70A YATES0.21
71E ZIWEI0.84
72GRADUATESSTEPHANIE AMADEI- WW/ND0.84
73SARAH BOTHERAS- ND/WW0.84
74KATE WIRGES- ND/WW0.84
75MONIQUE AMOS- BS0.84
76NICOLE ERWIN- BS0.84
77OKLEIGH MCGRATH- WW/ND0.84
78CAITLIN O'DONNELL- WW0.84
79CARLA ROTTEVEEL- WHC/BS0.84
80STUDENTSJODIE HOLMES0.63
81MEREDITH JOHNSON0.63
82CAROLYN KNIGHT0.63
83ELISHA POPE0.63
84Total Nursing EFT51.61
85Total ANUM EFT9.80
86
87ANUMAM20000
88PM20000
89Night20000
Roster
Cell Formulas
RangeFormula
B2B2=B1+27
D2D2=B1
E2:G2E2=D2+1
C84C84=SUM(C3:C83)
C85C85=SUM(C3:C16)
D87:G87D87=SUMPRODUCT(COUNTIF(D$3:D$16,Valid!$E$2:$E$163))
D88:G88D88=SUMPRODUCT(COUNTIF(D$3:D$16,Valid!$G$2:$G$163))
D89:G89D89=SUMPRODUCT(COUNTIF(D$3:D$16,Valid!$I$2:$I$163))
Named Ranges
NameRefers ToCells
Shifts=Roster!$D$3:$AE$22D87:D89
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D29:H29,K29:AE29Cell Valuecontains ""textNO
D29:H29,K29:AE29Cell Valuecontains ""textNO
D29:H29,K29:AE29Cell Valuecontains ""textNO
D31:AE31Cell Valuecontains ""textNO
D31:AE31Cell Valuecontains ""textNO
D31:AE31Cell Valuecontains ""textNO
D21:AE21Cell Valuecontains ""textNO
D21:AE21Cell Valuecontains ""textNO
D21:AE21Cell Valuecontains ""textNO
D17:N17Cell Valuecontains ""textNO
D17:N17Cell Valuecontains ""textNO
D17:N17Cell Valuecontains ""textNO
D79:W79,Y79:AE79Cell Valuecontains ""textNO
D79:W79,Y79:AE79Cell Valuecontains ""textNO
D79:W79,Y79:AE79Cell Valuecontains ""textNO
D80:O83,R80:V83,Y80:AC83Cell Valuecontains ""textNO
D80:O83,R80:V83,Y80:AC83Cell Valuecontains ""textNO
D80:O83,R80:V83,Y80:AC83Cell Valuecontains ""textNO
D19:AE19Cell Valuecontains ""textNO
D19:AE19Cell Valuecontains ""textNO
D19:AE19Cell Valuecontains ""textNO
D19:Q19,Y19:AC19Cell Valuecontains ""textNO
D19:Q19,Y19:AC19Cell Valuecontains ""textNO
D19:Q19,Y19:AC19Cell Valuecontains ""textNO
D89:AE89Cell Value<$C89textNO
D89:AE89Cell Value>$C89textNO
D89:AE89Cell Value=$C89textNO
D89:AE89Cell Valuecontains ""textNO
D89:AE89Cell Valuecontains ""textNO
D89:AE89Cell Valuecontains ""textNO
D72:W72,Y72:AE72Cell Valuecontains ""textNO
D72:W72,Y72:AE72Cell Valuecontains ""textNO
D72:W72,Y72:AE72Cell Valuecontains ""textNO
G16Cell Valuecontains ""textNO
G16Cell Valuecontains ""textNO
G16Cell Valuecontains ""textNO
D39:AE39Cell Valuecontains ""textNO
D39:AE39Cell Valuecontains ""textNO
D39:AE39Cell Valuecontains ""textNO
D40:AE40Cell Valuecontains ""textNO
D40:AE40Cell Valuecontains ""textNO
D40:AE40Cell Valuecontains ""textNO
D15:H15Cell Valuecontains ""textNO
D15:H15Cell Valuecontains ""textNO
D15:H15Cell Valuecontains ""textNO
D15:H15,P15:Q15Cell Valuecontains ""textNO
D15:H15,P15:Q15Cell Valuecontains ""textNO
D15:H15,P15:Q15Cell Valuecontains ""textNO
D13:J13,P13:AE13Cell Valuecontains ""textNO
D13:J13,P13:AE13Cell Valuecontains ""textNO
D13:J13,P13:AE13Cell Valuecontains ""textNO
D13:H13,P13:Q13Cell Valuecontains ""textNO
D13:H13,P13:Q13Cell Valuecontains ""textNO
D13:H13,P13:Q13Cell Valuecontains ""textNO
D11:J11,P11:AE11Cell Valuecontains ""textNO
D11:J11,P11:AE11Cell Valuecontains ""textNO
D11:J11,P11:AE11Cell Valuecontains ""textNO
P11:Q11,D11:H11Cell Valuecontains ""textNO
P11:Q11,D11:H11Cell Valuecontains ""textNO
P11:Q11,D11:H11Cell Valuecontains ""textNO
D3:W3Cell Valuecontains ""textNO
D3:W3Cell Valuecontains ""textNO
D3:W3Cell Valuecontains ""textNO
D16:F16,K16:Q16,H16Cell Valuecontains ""textNO
D16:F16,K16:Q16,H16Cell Valuecontains ""textNO
D16:F16,K16:Q16,H16Cell Valuecontains ""textNO
D85:AE85Cell Valuecontains ""textNO
D85:AE85Cell Valuecontains ""textNO
D85:AE85Cell Valuecontains ""textNO
D84:AE84Cell Valuecontains ""textNO
D84:AE84Cell Valuecontains ""textNO
D84:AE84Cell Valuecontains ""textNO
D23:AE24Cell Valuecontains ""textNO
D23:AE24Cell Valuecontains ""textNO
D23:AE24Cell Valuecontains ""textNO
D30:H30,K30:AE30Cell Valuecontains ""textNO
D30:H30,K30:AE30Cell Valuecontains ""textNO
D30:H30,K30:AE30Cell Valuecontains ""textNO
D25:X25Cell Valuecontains ""textNO
D25:X25Cell Valuecontains ""textNO
D25:X25Cell Valuecontains ""textNO
E9:J9,P9:AE9,I12:J12,I4:J10Cell Valuecontains ""textNO
E9:J9,P9:AE9,I12:J12,I4:J10Cell Valuecontains ""textNO
E9:J9,P9:AE9,I12:J12,I4:J10Cell Valuecontains ""textNO
D14:J14,P14:AE14Cell Valuecontains ""textNO
D14:J14,P14:AE14Cell Valuecontains ""textNO
D14:J14,P14:AE14Cell Valuecontains ""textNO
P7:Q7,E7:J7Cell Valuecontains ""textNO
P7:Q7,E7:J7Cell Valuecontains ""textNO
P7:Q7,E7:J7Cell Valuecontains ""textNO
D28:H28Cell Valuecontains ""textNO
D28:H28Cell Valuecontains ""textNO
D28:H28Cell Valuecontains ""textNO
D12:J12,P12:AE12Cell Valuecontains ""textNO
D12:J12,P12:AE12Cell Valuecontains ""textNO
D12:J12,P12:AE12Cell Valuecontains ""textNO
D20:AE20Cell Valuecontains ""textNO
D20:AE20Cell Valuecontains ""textNO
D20:AE20Cell Valuecontains ""textNO
D18:H18,K18:AE18Cell Valuecontains ""textNO
D18:H18,K18:AE18Cell Valuecontains ""textNO
D18:H18,K18:AE18Cell Valuecontains ""textNO
D4:W5,P12:Q12,D12:H12,D16:F16,K16:Q16,D4:H10,P4:Q10,H16Cell Valuecontains ""textNO
D4:W5,P12:Q12,D12:H12,D16:F16,K16:Q16,D4:H10,P4:Q10,H16Cell Valuecontains ""textNO
D4:W5,P12:Q12,D12:H12,D16:F16,K16:Q16,D4:H10,P4:Q10,H16Cell Valuecontains ""textNO
D87:AE88,D92:AE92,D94:AE94,D96:AE96Cell Value<$C87textNO
D87:AE88,D92:AE92,D94:AE94,D96:AE96Cell Value>$C87textNO
D87:AE88,D92:AE92,D94:AE94,D96:AE96Cell Value=$C87textNO
E10:J10,D6:H6,J6,Y4:AD5,U36:Y36,AB36:AE36,K36:L36,V28:AE28,E8:J8,P8:V8,U16:V16,P6:X6,P10:AE10,D26:AC26,D20:Q20,I27:AE27,K35:AE35,Y20:AC20,I30:J30,Y16:AB16,AD16:AE16,D90:AE90,D14:Q14,AD122:AE126,W122:X126,P122:Q126,I122:J126,I118:J120,P118:Q120,W118:X120Cell Valuecontains ""textNO
E10:J10,D6:H6,J6,Y4:AD5,U36:Y36,AB36:AE36,K36:L36,V28:AE28,E8:J8,P8:V8,U16:V16,P6:X6,P10:AE10,D26:AC26,D20:Q20,I27:AE27,K35:AE35,Y20:AC20,I30:J30,Y16:AB16,AD16:AE16,D90:AE90,D14:Q14,AD122:AE126,W122:X126,P122:Q126,I122:J126,I118:J120,P118:Q120,W118:X120Cell Valuecontains ""textNO
E10:J10,D6:H6,J6,Y4:AD5,U36:Y36,AB36:AE36,K36:L36,V28:AE28,E8:J8,P8:V8,U16:V16,P6:X6,P10:AE10,D26:AC26,D20:Q20,I27:AE27,K35:AE35,Y20:AC20,I30:J30,Y16:AB16,AD16:AE16,D90:AE90,D14:Q14,AD122:AE126,W122:X126,P122:Q126,I122:J126,I118:J120,P118:Q120,W118:X120Cell Valuecontains ""textNO
Cells with Data Validation
CellAllowCriteria
B1Any value
D3:AE15List=Valid!$A$2:$A$17
D16:AB16List=Valid!$A$2:$A$17
D17:AE71List=Valid!$A$2:$A$17
D72:W79List=Valid!$A$2:$A$17
D80:AE83List=Valid!$A$2:$A$17
D84:AE86List=Valid!$A$2:$A$15
 
Upvote 0
Welcome to the board! Can you describe:
  1. What is on the Valid sheet in the range $E$2:$I$163. You don't need to post the entire range, but a small sample of it, or a description of the contents would be helpful.
  2. It appears that you want to limit the number of Leadership staff to 2? Is that what the 2's mean in C87:C89? And is that why you reference D$3:D$16, E$3:E$16, etc. in the COUNTIF formula?
  3. Are you not intending to count other staff, say RM's, Graduates, Students?
  4. What is meant by a "D" shift? Does this mean a group of people comprise a shift and you'd like to pick so many to fill a "shift"...I'm unclear on what steps are taken to staff a shift.
  5. The SUMPRODUCT formula, as written, does not make sense. The COUNTIF formula used as an argument will return a single number, and SUMPRODUCT will simply return that same number. I'm trying to understand how you want to use this sheet. Do you want to place some type of text in a cell indicating that a person is assigned to some area during some time (AM/PM perhaps) on certain days?
  6. Does the text beside names indicate additional constraints that need to be taken into account? For example, "NDx2/fortnight" means what?...a Night shift or a Day shift twice each fortnight?
 
Upvote 0
Welcome to the board! Can you describe:
  1. What is on the Valid sheet in the range $E$2:$I$163. You don't need to post the entire range, but a small sample of it, or a description of the contents would be helpful.
  2. It appears that you want to limit the number of Leadership staff to 2? Is that what the 2's mean in C87:C89? And is that why you reference D$3:D$16, E$3:E$16, etc. in the COUNTIF formula?
  3. Are you not intending to count other staff, say RM's, Graduates, Students?
  4. What is meant by a "D" shift? Does this mean a group of people comprise a shift and you'd like to pick so many to fill a "shift"...I'm unclear on what steps are taken to staff a shift.
  5. The SUMPRODUCT formula, as written, does not make sense. The COUNTIF formula used as an argument will return a single number, and SUMPRODUCT will simply return that same number. I'm trying to understand how you want to use this sheet. Do you want to place some type of text in a cell indicating that a person is assigned to some area during some time (AM/PM perhaps) on certain days?
  6. Does the text beside names indicate additional constraints that need to be taken into account? For example, "NDx2/fortnight" means what?...a Night shift or a Day shift twice each fortnight?
Hi Kirk! thankyou for having a look at the predicament i'm in.
1. I've got a separate sheet with codes that staff can choose from when assigning their shifts.
2. Yes, I need 2 leadership staff each shift 'D' 'E'' and 'N' but what is currently happening is staff keep putting themselves for shift even when there is 2 people on as there is no lock outs
3. The other staff have to be counted in a different area of the sheet, so once i've worked out the correct formula I'll apply the same to their cells.
4. 'D' means 'day shift, 0700-1530'
5. I didn't think it made sense either! lol (beginner excel user right here!) I want the staff to choose from the drop down boxes their shift, then once 2 of the same shifts have been selected for that day i want it to come up with an error message for people to choose another shift instead
6. the txt next to their names is from the roster manager to indicate if they need to work night shift at all in that fortnight/month.

Ill include the xl2bb of the other sheet that its pulling the codes from
Roster Template.xlsx
ABCDE
1Valid Shift CodesDay Shift CodesNight Shift CodesEFT Codes07:00 - 16:30
2DDNND
3MWDSNCNCNCD
4EENSNNSNDSS
5NMWADO
6OffDSS
7MDESS
8ADOMD
9LNSS
10PDNuts
Valid
 
Upvote 0
If I understand correctly, what you want to do is a bit tricky. Can you confirm that you are using Excel 2013? There are some features in Excel 365 that would simplify some of this. Here is one idea...and I'm showing it on one worksheet to make it easier to follow.
  1. You have Valid Shift Codes on the Valid worksheet in column A.
  2. A large set of helper columns is established somewhere, perhaps on the same Valid worksheet, where each day of the week/month has its own column (see columns G:J). The formula in these columns is intended to create a customized list of available shift codes for each day, which depends on whether two people have already signed up for D, E, or N shifts. The formula looks at your Roster Scheduling Input table (simulated in columns Q:T) and performs a count of D, E, and N entries for that particular day, and if the count = 2, then the formula replaces the D, E, or N list item with a blank. Now if you referenced this list of items for your drop down list selection using Data Validation, the blanks in the middle of the list will look awkward, so...
  3. Another large set of helper columns is established (see columns L:O), again one column for each day of the week/month, and these columns have a formula that consolidates the pared down list by removing the blanks from the middle of the list...although I didn't bother trying to write the formula to prevent the blanks from appearing at the end of the list. Your Roster scheduling sheet then references this consolidated list of available options using the Data Validation "list" feature. So one two people sign up for D, E, or N on a given day, that option is no longer available for anyone else to choose...unless someone deletes an existing entry in the roster table.
  4. If desired, you could double up on these formulas and include one inside the other, but then they become very messy (see columns V:Y for an example).
  5. I believe you will need to confirm these formulas with Ctrl-Shift-Enter when entering them to make them array formulas.
MrExcel_20220524.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Valid Shift CodesDay Shift CodesNight Shift CodesEFT Codes07:00 - 16:30Reduction of D,E,N Choices Based on Counts from Roster (Unique for Each Day)Elimination of Blanks for Cleaner Drop Down List Selections (also Unique for Each Day)Notional Scheduling Days on Roster Sheet
2DDNND9999
3MWDSNCNCNCD7/47/57/67/77/47/57/67/77/47/57/67/77/47/57/67/7
4EENSNNSNDSS D DMWDMWDDMWDMWD
5NMWADOMWMWMWMWEMWEMWMWNEMWEMW
6OffDSSEEEOffNOffEDOffNOffE
7MDESSNNMOffMNNDMOffMN
8ADOMDOffOffOffOffAOMDAOOffEAOMDAOOff
9LNSSMMDMMDLADOLMDENLADOLMD
10PDNutsAOADOAOADOPLPADONPLPADO
11LLLL PD LE PD L
12PPDPPD   PD   PD
13D
14
15
Valid
Cell Formulas
RangeFormula
G2:J2G2=COUNTA(G4:G12)
G3G3=Roster!D2
H3:J3,W3:Y3,R3:T3,M3:O3H3=G3+1
G4:J12G4=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,"D",IF(COUNTIF(Q$4:Q$15,"D")=2,"","D")),"E",IF(COUNTIF(Q$4:Q$15,"E")=2,"","E")),"N",IF(COUNTIF(Q$4:Q$15,"N")=2,"","N"))
L3L3=Roster!D2
L4:O12L4=IFERROR(INDEX(G$4:G$12,SMALL(IF((G$4:G$12<>""),ROW(INDIRECT("1:"&G$2)),""),ROW(G1))),"")
V3V3=Roster!D2
V4:Y12V4=IFERROR(INDEX(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,"D",IF(COUNTIF(Q$4:Q$15,"D")=2,"","D")),"E",IF(COUNTIF(Q$4:Q$15,"E")=2,"","E")),"N",IF(COUNTIF(Q$4:Q$15,"N")=2,"","N")),SMALL(IF((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,"D",IF(COUNTIF(Q$4:Q$15,"D")=2,"","D")),"E",IF(COUNTIF(Q$4:Q$15,"E")=2,"","E")),"N",IF(COUNTIF(Q$4:Q$15,"N")=2,"","N"))<>""),ROW(INDIRECT("1:"&G$2)),""),ROW(G1))),"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
Q4:T15List=L$4:L$12
 
Last edited:
Upvote 0
If I understand correctly, what you want to do is a bit tricky. Can you confirm that you are using Excel 2013? There are some features in Excel 365 that would simplify some of this. Here is one idea...and I'm showing it on one worksheet to make it easier to follow.
  1. You have Valid Shift Codes on the Valid worksheet in column A.
  2. A large set of helper columns is established somewhere, perhaps on the same Valid worksheet, where each day of the week/month has its own column (see columns G:J). The formula in these columns is intended to create a customized list of available shift codes for each day, which depends on whether two people have already signed up for D, E, or N shifts. The formula looks at your Roster Scheduling Input table (simulated in columns Q:T) and performs a count of D, E, and N entries for that particular day, and if the count = 2, then the formula replaces the D, E, or N list item with a blank. Now if you referenced this list of items for your drop down list selection using Data Validation, the blanks in the middle of the list will look awkward, so...
  3. Another large set of helper columns is established (see columns L:O), again one column for each day of the week/month, and these columns have a formula that consolidates the pared down list by removing the blanks from the middle of the list...although I didn't bother trying to write the formula to prevent the blanks from appearing at the end of the list. Your Roster scheduling sheet then references this consolidated list of available options using the Data Validation "list" feature. So one two people sign up for D, E, or N on a given day, that option is no longer available for anyone else to choose...unless someone deletes an existing entry in the roster table.
  4. If desired, you could double up on these formulas and include one inside the other, but then they become very messy (see columns V:Y for an example).
  5. I believe you will need to confirm these formulas with Ctrl-Shift-Enter when entering them to make them array formulas.
MrExcel_20220524.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1Valid Shift CodesDay Shift CodesNight Shift CodesEFT Codes07:00 - 16:30Reduction of D,E,N Choices Based on Counts from Roster (Unique for Each Day)Elimination of Blanks for Cleaner Drop Down List Selections (also Unique for Each Day)Notional Scheduling Days on Roster Sheet
2DDNND9999
3MWDSNCNCNCD7/47/57/67/77/47/57/67/77/47/57/67/77/47/57/67/7
4EENSNNSNDSS D DMWDMWDDMWDMWD
5NMWADOMWMWMWMWEMWEMWMWNEMWEMW
6OffDSSEEEOffNOffEDOffNOffE
7MDESSNNMOffMNNDMOffMN
8ADOMDOffOffOffOffAOMDAOOffEAOMDAOOff
9LNSSMMDMMDLADOLMDENLADOLMD
10PDNutsAOADOAOADOPLPADONPLPADO
11LLLL PD LE PD L
12PPDPPD   PD   PD
13D
14
15
Valid
Cell Formulas
RangeFormula
G2:J2G2=COUNTA(G4:G12)
G3G3=Roster!D2
H3:J3,W3:Y3,R3:T3,M3:O3H3=G3+1
G4:J12G4=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,"D",IF(COUNTIF(Q$4:Q$15,"D")=2,"","D")),"E",IF(COUNTIF(Q$4:Q$15,"E")=2,"","E")),"N",IF(COUNTIF(Q$4:Q$15,"N")=2,"","N"))
L3L3=Roster!D2
L4:O12L4=IFERROR(INDEX(G$4:G$12,SMALL(IF((G$4:G$12<>""),ROW(INDIRECT("1:"&G$2)),""),ROW(G1))),"")
V3V3=Roster!D2
V4:Y12V4=IFERROR(INDEX(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,"D",IF(COUNTIF(Q$4:Q$15,"D")=2,"","D")),"E",IF(COUNTIF(Q$4:Q$15,"E")=2,"","E")),"N",IF(COUNTIF(Q$4:Q$15,"N")=2,"","N")),SMALL(IF((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,"D",IF(COUNTIF(Q$4:Q$15,"D")=2,"","D")),"E",IF(COUNTIF(Q$4:Q$15,"E")=2,"","E")),"N",IF(COUNTIF(Q$4:Q$15,"N")=2,"","N"))<>""),ROW(INDIRECT("1:"&G$2)),""),ROW(G1))),"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
Q4:T15List=L$4:L$12
 
Upvote 0
Wow! this is massive! I've double checked and i've actually got excel 365 so im hoping this will help me to simply the input of the formulas.
1. yes i've got vaild shift codes on the sitting in the vaild sheet column A
2. If i put this formula into the data validation for every column it will hopefully work?
G4:J12G4=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$2:$A$10,"D",IF(COUNTIF(Q$4:Q$15,"D")=2,"","D")),"E",IF(COUNTIF(Q$4:Q$15,"E")=2,"","E")),"N",IF(COUNTIF(Q$4:Q$15,"N")=2,"","N"))
3. do i just use the Ctrl-Shift-Enter when entering them to make them array formulas when its within the cell or is this when I'm entering as a data validation?

thankyou so much again for all your time and effort on this. So So appreciated
 
Upvote 0
The data validation is very basic. The drop down list referenced by Data Validation in my example is just:
Excel Formula:
=L$4:L$12
and then copied across to the various roster sheet cells (the lack of the $sign in front of the column indicator (L) causes the Data Validation to automatically update to =K$4:K$12 for the next column, and so on). The real work to establish the lists is done by the other two formulas--both are needed, and the two can be combined into one if: 1) you don't mind messy formulas, and 2) you don't want to deal with extra helper columns. But if you will be using this on a computer with Excel 365, I'll offer a trimmed down version of the formula. I think with this approach, you will still need one set of helper columns to create the pared-down list of options using the combo SUBSTITUTE and list consolidation formula that is used by the Data Validation in the scheduling cells. I'll experiment a bit to see if Data Validation will accept the complicated formula, but I'm nearly certain it will not, which is why I believe you'll need the helper columns. About the array formula entry...if you are using Excel 365, you should not need to enter the formula with Ctrl-Shift-Enter, as 365 natively handles this. But there are other functions that are more attractive for doing this in 365, so I'll suggest some enhancements.

On your roster scheduling worksheet, do you plan a fortnight, month, or longer at a time? Unfortunately, the helper columns will be as wide as the roster table, but they can be hidden from view. I'll be stepping away for a while, but will check back in later.
 
Upvote 0
The data validation is very basic. The drop down list referenced by Data Validation in my example is just:
Excel Formula:
=L$4:L$12
and then copied across to the various roster sheet cells (the lack of the $sign in front of the column indicator (L) causes the Data Validation to automatically update to =K$4:K$12 for the next column, and so on). The real work to establish the lists is done by the other two formulas--both are needed, and the two can be combined into one if: 1) you don't mind messy formulas, and 2) you don't want to deal with extra helper columns. But if you will be using this on a computer with Excel 365, I'll offer a trimmed down version of the formula. I think with this approach, you will still need one set of helper columns to create the pared-down list of options using the combo SUBSTITUTE and list consolidation formula that is used by the Data Validation in the scheduling cells. I'll experiment a bit to see if Data Validation will accept the complicated formula, but I'm nearly certain it will not, which is why I believe you'll need the helper columns. About the array formula entry...if you are using Excel 365, you should not need to enter the formula with Ctrl-Shift-Enter, as 365 natively handles this. But there are other functions that are more attractive for doing this in 365, so I'll suggest some enhancements.

On your roster scheduling worksheet, do you plan a fortnight, month, or longer at a time? Unfortunately, the helper columns will be as wide as the roster table, but they can be hidden from view. I'll be stepping away for a while, but will check back in later.
Alrighty, I think i've got my head around that information. It's ok if there are helper columns as long as they can be hidden as to not confuse the staff when they're using it. Our roster is 28days long and is released for staff to put in their shifts 10weeks in advance.
Thanks again for all or your efforts
 
Upvote 0
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
ABCDEFGHIJKLMNOP
1Valid Shift CodesDay Shift CodesNight Shift CodesEFT Codes07:00 - 16:30Reduction of D,E,N Choices Based on Counts from Roster (Filtered Unique for Each Day)Notional Scheduling Days on Roster Sheet
2DDNND7/47/57/67/77/47/57/67/7
3MWDSNCNCNCDMWDMWDOff
4EENSNNSNDSSOffMWEMWMWN
5NMWADOMDNOffE
6OffDSSADOOffMDNND
7MDESSLMDADOOffE
8ADOMDPDADOLMDDN
9LNSSLPDADON
10PDNutsPDLEE
11PDL
12ED
13D
14
Valid
Cell Formulas
RangeFormula
G2G2=Roster!D2
H2:J2,N2:P2H2=G2+1
G3:G8,J3:J11,I3:I9,H3:H10G3=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
CellAllowCriteria
M3:P14List=G$3#
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,455
Messages
6,178,768
Members
452,875
Latest member
Disastrouscoder

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top