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 | | | | | | | | | | | | | | | | |
|---|
|
|---|
