Google Sheets - Data Validation - Conditional based on Column in another sheet

Im currently designing a menu for a food festival. I use google sheets. I have a sheet filled with food choices. the menu for a given week should not have food items from the previous weeks. This is a mandatory requirement & I'm not able to get the drop-down if I use Data validation & custom formula.

I use =FILTER('Item Suggestions'!A:A,'Item Suggestions'!E:E="Y") as the custom formula for validating the data.

Is there any other way (or a tweak to get the data validation drop down) to get the drop-down & keep the drop-down list filtered?

Each of the columns in the "Menu" sheet should pick from column A of the "Item Suggestions" sheet. But the data should be based on column E. If Column E is Y, then that respective data in Column A should be shown in the drop-down

Menu Sheet:

Item Suggestions sheet:

Validation Helper Columns

I added some 'helper' columns for the validation.

1. They can be on the same sheet or a different one.
2. There is one for each course: Appetizer, Main, Dessert, and Drink. I assume Main and Course 2 both share the same dishes.
3. The FILTER formula would return an array of Dishes that match the correct Course, and haven't yet been used:

=IFERROR(FILTER(Dishes,Courses=thisCourse,isUsed<>"Y"),"-")

4. For the validation rule,
   • the criteria would be "Dropdown (from a range)" with the range being the appropriate helper column for each Course
   • The "Apply to range" value would be the appropriate Course column in your Menu table
5. Please note that all populated menu items will 'always' show the error flag (red triangle in the top right corner). This is because the moment they are used, they are no longer valid values. This doesn't affect the functionality of the menu dropdowns. Used menu items will be filtered from the dropdowns, and you will not be able to add a used menu item manually with the dropdown properties set to reject. Just a visual distraction.

Dropdown Formula

Single Formula

Will generate all dropdowns at once and centralizes modifications

=BYCOL(M2:P2, LAMBDA(c, 
   IFERROR(FILTER(G:G,H:H=c,K:K<>"Y"),"-")))

Individual Formula

Needs to be manually copied to each column

=IFERROR(FILTER($G:$G,$H:$H=M2,$K:$K<>"Y"),"-")

_{Filtering Formula}<br>
[<img src="https://i.stack.imgur.com/YzgOC.png" width="550" />](https://i.stack.imgur.com/YzgOC.png "click to enlarge")

_{Dropdown with Filtered Dishes}<br>
[<img src="https://i.stack.imgur.com/4AJM1.png" width="550" />](https://i.stack.imgur.com/4AJM1.png "click to enlarge")

Formula to Mark Dishes When Used

1. Your Master List of dishes includes a column to mark if a dish has been used previously
2. Your menu's dropdowns are based on that and it makes sense to update the "used/not used" status dynamically when a dish is added or removed from a menu.
3. This can be achieved using a formula in the Master List that marks each dish based on whether it exists in the menu.

Dish "Is Used" Formula

Note that the 'Single Formula' includes the column heading "Used in previous weeks?".

• This was intentional in order to place the formula a line above the Master List data
• Offsetting the formula from the data by a row, allows the data to be sorted without impacting the formula. For example, you could sort the Master List by any of Dishes, Courses, Allergens, or Type

Single Formula

={"Used in previous weeks?";
  BYROW(G3:G51, LAMBDA(r, 
    IFERROR(IF(ROWS(FILTER(r,COUNTIF(A:E,r)))>0,"Y"))))}

Individual Formulas

Needs to be copied to each row

=IFERROR(IF(ROWS(FILTER(G3,COUNTIF(A:E,G3)))>0,"Y"))

_{Sorted Asc. by Dishes}<br>
[<img src="https://i.stack.imgur.com/vcxEI.png" width="550" />](https://i.stack.imgur.com/vcxEI.png "click to enlarge")<br><br>

_{Sorted Asc. by Courses then Dishes}<br>
[<img src="https://i.stack.imgur.com/Kp8bq.png" width="550" />](https://i.stack.imgur.com/Kp8bq.png "click to enlarge")<br><br>