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

 

[sandyandy5]

I don't know if you have XL2BB available where you are, but if not, can you cut and paste the formula from a few cells so that I can examine them...just let me know which cells (e.g., the upper left cell of the roster table and the upper left of the helper table that corresponds to the same day). And give one more thing a try. In the helper table, if you're not seeing it populate correctly, select the topmost formula in that column, hit F2 to go into formula editing mode, and then re-confirm the formula by entering it as an array formula by hitting Ctrl-Shift-Enter...you should see curly brackets appear around the formula in the formula bar. Then select that cell again and drag it straight down the helper column.

Yes i've got XL2BB but first time using it. Here is the formula for the helper cell in M3 on the valid sheet after i've gone to f2 and ctrl+shift+enter

Roster Template.xlsx
	L	M	N	O
1		ANUM helper comumns
2		1	2	3
3		D
4
5
6
Valid

Cell Formulas
Range	Formula
O2	O2	=N2+1
M3	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!D4:$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))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

See my last post about pulling that formula down the column...I don't see where that has been done yet. But before you do that, go back and edit the formula again (e.g. delete the last parentheses and then retype it) and then Enter the formula normally as a standard formula (without the Ctrl-Shift-Enter). Then pull the formula down the column and see what happens.

 

[sandyandy5]

Current situation.... with the data validation in place. I can still only see 'D' in the drop down box when back in the roster sheet despite all the codes been entered into the helper column now. have i entered something incorrectly?

Roster Template.xlsx
	K	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
Valid

Cell Formulas
Range	Formula
M3:M13	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!D4:$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))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

This is progress! First let's nail down the helper column and confirm that the array formula is necessary. In my last post, I mentioned deleting the last character and then retyping it and hitting Enter. That will convert the formula back to a standard one, rather than an array formula. After doing that, pull the standard formula down the column and see if it populates correctly. If it does, then the array formula is not needed...which would be preferred.

Then in your roster table, which must be Roster!$D$4 for the 1st cell on this day, go to Data Validation and copy the "Source" formula that you are using. I'd like to see that.

 

[sandyandy5]

See my last post about pulling that formula down the column...I don't see where that has been done yet. But before you do that, go back and edit the formula again (e.g. delete the last parentheses and then retype it) and then Enter the formula normally as a standard formula (without the Ctrl-Shift-Enter). Then pull the formula down the column and see what happen

This is progress! First let's nail down the helper column and confirm that the array formula is necessary. In my last post, I mentioned deleting the last character and then retyping it and hitting Enter. That will convert the formula back to a standard one, rather than an array formula. After doing that, pull the standard formula down the column and see if it populates correctly. If it does, then the array formula is not needed...which would be preferred.

Then in your roster table, which must be Roster!$D$4 for the 1st cell on this day, go to Data Validation and copy the "Source" formula that you are using. I'd like to see that.

So I've deleted the last character off the formula '}' and pressed enter and dragged it down the column and its populating correctly within the column. The drop down box in roster still is only giving me 'd' as the only option.
My source formula is =Valid!M$3#

Roster Template.xlsx
	K	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:M15	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!D4:$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))),"")
M16:M19	M16	=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$16:$D17,"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($A14))),"")

 

[KRice]

Oh...that's the issue. That Source formula is specific to Excel 365 and beyond. The # operator tells Excel 365 to "spill" the entire array. Excel 2013 will not understand that. Review my suggestion in post #25. I would suggest the =OFFSET(W$3,,,SUM(N(W$3:W$11<>"")),1) approach mentioned. For your setup, that formula would be:

=OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1)

Just confirm that Valid!M$3:M$19 is the entire column of possible shift codes for that day, even if there are blanks at the bottom of that column. If that checks out, then paste that formula into the upper roster table cell and see if it works okay. If it does, then copy that cell and Paste Special > Validation into the other cells in that same column on the roster table.

Edit: I didn't look far enough...looks like the range extends to M19....formula updated.

 

[sandyandy5]

Oh...that's the issue. That Source formula is specific to Excel 365 and beyond. The # operator tells Excel 365 to "spill" the entire array. Excel 2013 will not understand that. Review my suggestion in post #25. I would suggest the =OFFSET(W$3,,,SUM(N(W$3:W$11<>"")),1) approach mentioned. For your setup, that formula would be:

=OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1)

Just confirm that Valid!M$3:M$19 is the entire column of possible shift codes for that day, even if there are blanks at the bottom of that column. If that checks out, then paste that formula into the upper roster table cell and see if it works okay. If it does, then copy that cell and Paste Special > Validation into the other cells in that same column on the roster table.

Edit: I didn't look far enough...looks like the range extends to M19....formula updated.

Sorry Kirk, just clarifying am i pasting =OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1) into this 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))),"")

Or am i pasting into data validation? =OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1)

 

[KRice]

No problems...no. Your helper table formula that creates the dropdown list items seems to be working fine. The only issue now is that your Data Validation on your roster scheduling table needs to be fixed. It is not correctly referencing the dropdown helper table. So go back to the roster table and click on the uppermost cell that corresponds to the day of interest, then click on Data > Data Validation, and overwrite that last formula you posted (My source formula is =Valid!M$3#) with
=OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1)
That should work. If it does work for that cell, then copy the cell and Paste Special > Validation into the other cells in the roster table.

 

[sandyandy5]

Sorry Kirk, just clarifying am i pasting =OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1) into this 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))),"")

Or am i pasting into data validation? =OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1)

I've pasted it into the data validation for the roster sheet and WHAAMY we get a visual on the full list! woohoo! AND it takes it away from the list so the staff member has no option to choose which would give them a double up!

 

[sandyandy5]

No problems...no. Your helper table formula that creates the dropdown list items seems to be working fine. The only issue now is that your Data Validation on your roster scheduling table needs to be fixed. It is not correctly referencing the dropdown helper table. So go back to the roster table and click on the uppermost cell that corresponds to the day of interest, then click on Data > Data Validation, and overwrite that last formula you posted (My source formula is =Valid!M$3#) with
=OFFSET(Valid!M$3,,,SUM(N(Valid!M$3:M$19<>"")),1)
That should work. If it does work for that cell, then copy the cell and Paste Special > Validation into the other cells in the roster table.

Works like a dream!
What a whizz you are!
I'll just get the staff to use the mouse to select their shift from the dropdown box rather than the keyboard or they will get an error message when there is one left if trying to select this with the keyboard.