Google Sheets - Data Validation - Conditional based on Column in another sheet
Validation Helper Columns
I added some 'helper' columns for the validation.

They can be on the same sheet or a different one.
There is one for each course: Appetizer, Main,  Dessert, and Drink. I assume Main and Course 2 both share the same dishes.
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"),"-")


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


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

Dropdown with Filtered Dishes

Formula to Mark Dishes When Used

Your Master List of dishes includes a column to mark if a dish has been used previously
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.
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

Sorted Asc. by Courses then Dishes


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:




Column  A
Column B
Column C
Column D
Column E




Appetizers
Mains
Course 2
Dessert
Drinks


Soup
Chicken
Creamy Pasta
Strawberry Mousse
Fire and Ice


Broccoli
Rice & Curry
Roti & Subzi
Icecream Sundae
Mojito




Item Suggestions sheet:




Column A
Column B
Column C
Column D
Column E




Dish
Course
Allergens
Type
Used in Previous weeks?


Creamy Pasta
Main
Gluten
Vegetarian
Y


Chocolate Marble Cake
Dessert
Wheat
Vegan
N



