DEPENDENT PULL DOWN FRUSTRATION EVEN AFTER VIEWING & TRYING MULTIPLE VIDEO'S & THEIR RESPECTIVE METHODS

Question

IN ONLINE EXCEL 365, I AM ATTEMPTING TO BUILD A SERIES OF DEPENDENT PULL DOWN LISTS. I HAVE NAMED RANGES FOR EACH PULL DOWN IN A COMMON TABLE. I HAVE PUT UNDERSCORES AT ALL NAME SPACES. THE 1ST PULLDOWN LIST IS EASY TO MAKE THROUGH THE DATA VALIDATION PROCESS. THE 2ND PULLDOWN LIST IS FRUSTRATING & IS REJECTING THE =INDIRECT(A1) COMMAND AND THE =XLOOKUP(ITEM, RANGE, RANGE, INF)

Answer 1

Dear @Mark Webb,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your description, you are attempting to create dependent drop‑down lists in Excel Online using named ranges with formulas such as =INDIRECT(A1), but the formula is being rejected or not working as expected.

Have you tried to switch to Excel desktop app version to check whether the issue persists or not?

It would be helpful if you could share a screenshot of your data so I can better understand your scenario and provide more effective support.

In the meantime, based on a similar case posted by another user, you may refer to the following: Excel INDIRECT Function for Dropdown Issue in Online Version, Works Fine in Desktop

Looking forward to your update.

Answer 2

For dependent drop-downs in Excel for the web, the data validation formula must return a valid list/range reference. The rejection of =INDIRECT(A1) and =XLOOKUP(...) usually means the formula is not resolving to a valid range name or list.

Use this pattern:

1. Ensure the first drop-down (in A1) returns text that exactly matches a defined name.
   • Example: Named ranges: Fruits, Vegetables, Drinks.
   • First list (A1) contains: Fruits, Vegetables, Drinks.
2. Define named ranges for each dependent list.
   • Select the list for “Fruits” → Formulas → Name Manager → Name: Fruits.
   • Repeat for each category. Avoid spaces; use underscores if needed, and make sure the text in A1 matches the name exactly (e.g., Fruits_and_Veg in both A1 and the Name Manager).
3. Set data validation for the dependent cell (e.g., B1):
   • Select B1.
   • Data → Data Validation.
   • Allow: List.
   • Source: =INDIRECT($A$1).
4. Test:
   • Choose Fruits in A1.
   • B1 should now show the items in the named range Fruits.

Key points:

• INDIRECT(A1) must evaluate to a valid named range or range address. If A1 contains Item 1 but the named range is Item_1, the formula fails. Make the text in A1 and the named range name identical (including underscores).
• XLOOKUP is not needed for standard dependent lists. Data validation lists must be a direct range reference or a formula that returns a range; XLOOKUP returns values, not a range reference.
• If the first list is based on a table column, ensure the dependent named ranges are standard ranges or structured references that INDIRECT can resolve.

If the second list still fails, check:

• A1 contains no leading/trailing spaces.
• The named range exists and is workbook-scope.
• The data validation Source box contains =INDIRECT($A$1) (with the leading = and no extra quotes).

For more background on creating and maintaining drop-down lists and data validation, see the referenced articles.

---

References:

• Add or remove items from a drop-down list
• Apply data validation to cells
• Help