Andrea, give this a try. You may want to jump into the more complex version since it will align better with your existing structure. The more complex version is shown in columns V:Y where the dropdown lists are formed for each of the four days in this working example. You will see some redundancy in the formula, which is something that happens when a quantity or an array of values is needed in multiple places. In Excel 365, this redundancy can be greatly reduced using the LET function to assign the quantity/array formula to a name, and then the name can be used in subsequent formulas. But in this case, with Excel 2013, the embedded formula needs to be repeated...so after carefully adjusting the ranges in the first part of the formula (shown below in what I refer to as the "1st messy formula"), just copy and paste it over the redundant part that appears just after SMALL(IF((
1st messy formula found within main formula:
Excel Formula:
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,"")
Fundamentally then, the single formula approach for creating the dropdown lists takes this form, which isn't too bad to follow.
=IFERROR(INDEX(
1st messy formula , SMALL(IF((
1st messy formula <>""),ROW(INDIRECT("1:"&COUNTA($A$2:$A$10))),""),ROW($A1))),"")
As described previously, you will want to copy this across the 1st row of your dropdown list helper table so that each day's formula adjusts to the same day's range in the roster scheduling table. Then select all of the those top row cells and drag them to the bottom of the dropdown table...all the way to the bottom even if the formula begins to create blanks. This ensures that the formula is present and able to generate the complete dropdown list even if no shift codes are excluded...so the length of this dropdown table should match the length of the list found under the initial Valid Shift Codes column.
Then the roster scheduling table uses Data Validation in each column (for each day) to point to the relevant day in the helper dropdown list. I'm showing two approaches for doing the Data Validation. The simplest in shown in column Q for the data 7/4 where the entire dropdown list range is specified as the Source for the "list". The only issue with this is those blanks that appear at the bottom of the dropdown column in V9:V11 will appear as blanks at the bottom in your selection list. If you want to clean that up, you can go for the other option, shown in columns R:T where a formula is entered as the Source for the list in the Data Validation window. That formula counts the number of elements in the list and dynamically adjusts the length of the range returned to the Data Validation list. With either approach, pay attention to what is "fixed" (the $ signs) and what is relative since you will copy the cell with the data validation and then select the entire roster block where you want this invoked and perform a Paste Special > Validation...to paste the data validation rules everywhere. If done correctly, you will only have to enter the Data Validation window one time for the first cell and simply copy that validation everywhere within a common block that shares the same dropdown list table.
The other columns (G:J and L:O) represent a two-step approach, similar to what I described in an earlier post...but the single formula approach combines these same two steps into one. Let me know if you encounter any issues or have any questions.
