Conditional data validation drop downs

Hi uchidozie​, 

You can’t put an IF formula directly into the Data Validation dialog.

Data Validation lists must point to a range of cells or a named range. The way to solve this is to first build a filtered list of names based on availability, then link your drop‑down to that list.

Step 1: Set up your table

• Column A = Names
• Column B = Monday Morning availability (values like “Available” or “Not Available”)
• Column C = Monday Afternoon availability, and so on.

Step 2: Create a helper column (works in all Excel versions)

• In a blank column (say Column Z), enter: =IF(B2="Available", A2, "")
• Copy this down for all rows.
• This column now shows the name if they are available, or blank if not.

Step 3: Define a named range

• Select the helper column values.
• Go to Formulas → Define Name → call it something like AvailableNames.

Step 4: Link to Data Validation

• Select the cell where you want the drop‑down.
• Go to Data → Data Validation → List.
• In Source, type: =AvailableNames

Step 5: (Excel 365 shortcut) Use FILTER instead of helper column

• If you have Excel 365, you can use: =FILTER(A2:A100, B2:B100="Available")
• This spills out only the available names.
• Point your Data Validation list to this spilled range.

Step 6: Adjust for other time slots

• For Monday Afternoon (Column C), use: =FILTER(A2:A100, C2:C100="Available")

Key Point: Data Validation lists must reference a range of cells or a named range. You can’t type an IF directly into the Data Validation dialog, you need to prepare the list first.

Tip: Hide your helper columns after setting them up to keep the sheet clean.

Build a filtered list (using IF in a helper column or FILTER in Excel 365), then point your Data Validation drop‑down to that list. That’s the only way to make conditional drop‑downs work in Excel.

Hope this helps your scheduling setup!