Forum Discussion

uchidozie's avatar
uchidozie
Copper Contributor
Dec 15, 2025

Conditional data validation drop downs

I have a table with everyone's availability. Column A is names, Column B is Monday morning, Column C is Monday afternoon and so on. In another cell, I want a drop down list of names if they're available at that time. If( [Monday Morning] = "Available") put it in the drop down list and then do that for the whole week. When I try to do formulas in the data validation page it doesn't work.

excel
Excel for web

2 Replies

  • Olufemi7's avatar
    Olufemi7
    Brass Contributor
    Dec 18, 2025

    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!

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 15, 2025

    Hi

    Assuming your data is on Sheet1 with Names in A2:A5:

    Create a new Sheet2 (you can hide later):

    #1 On Sheet2 in A2 then copy right as necessary:

    =FILTER( Sheet1!$A2:$A5, Sheet1!B2:B5 = "available", "" )

    #2 Switch to Sheet1 > In Data Validation on B7 and copy right as necessary:

    =Sheet2!A$2#

    (Don't forget the # sign at the end)

    Sample at: https://1drv.ms/x/c/1cd824d35610aacb/IQBN8UpV0kxfQJ5AFzs1WF3hARCxMpkCqNADQBR6vtlVPMY?e=uaMDfX

Resources