INDIRECT function isn't working for my Dependent Dropdown List

Question

INDIRECT function isn't working for my Dependent Dropdown List

Elliot 20

I'm trying to create a dependent dropdown list on Excel (online on my Mac computer) via the Data Validation method but when I try using the indirect function, it keeps on giving me back an "error" notification. I even made the 3 types of food as lists and even tried them as tables.

If I have it written as =indirect($H$15) sometimes it works and sometimes it doesn't. I've followed a few other video tutorials and they always have it written without the $ signs but it still doesn't work. I've been trying to figure things out on my own for the past 2-3 weeks, but I still haven't found out what to do. The app on my computer is outdated (so it won't save my progress there) but this same method works just fine. Is there a bug for the online version for some reason? Because I did the same exact stuff on both but it won't do it at all on the online version.

Screenshot 2026-01-31 at 2.34.32 PM

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Marcin Policht 90,150 MVP Volunteer Moderator

As far as I can tell, the problem isn’t with the $ signs or even your use of INDIRECT itself—it’s how Excel Online handles named ranges and the INDIRECT function compared with the desktop version. In Excel for Mac/Windows, INDIRECT works with both direct cell references and named ranges, but Excel Online has some limitations: INDIRECT does not always work with dynamic named ranges or table references, especially if the ranges are not defined as named ranges in the Name Manager.

Right now, you have your lists in columns A, B, and C. To make =INDIRECT(H15) work in Excel Online, try the following:

First, give each of your lists a proper named range. Select the list under “Fruit” (A16:A18), go to Formulas → Name Manager → New, and name it exactly Fruit. Do the same for “Vegetable” (B16:B18) and “Meat” (C16:C18). Make sure the names exactly match what appears in H15—no extra spaces, no special characters, capitalization doesn’t matter.

After that, in the Data Validation for the Category column (I15 and below), set Allow to List, and in Source, write:

=INDIRECT(H15)

Do not use table references like Table1[Fruit]—Excel Online will reject that in INDIRECT. It only works with named ranges or direct cell references.

Also, check that your H15 values match the named ranges exactly. If there’s even an extra space before/after, INDIRECT will likely fail.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Elliot 20 Reputation points

2026-02-08T18:56:20.5233333+00:00

Hi! Thanks for getting back to me! And sorry for the delayed reply. I've been working on it this past week and been trying what you suggested. It still doesn't seem to work unfortunately no matter what I do. I continue to get that 'error' notification.

I even decided to make a new list called "Type" and included the 3 types of items for my "Type" Column. I also did just discover that the INDIRECT Function in the DATA VALIDATION method will work if I manually do each row with its own INDIRECT function. Like for example, for I15 I put "=INDIRECT($H$15)", I16 I put "=INDIRECT($H$16), and so on and so forth.

So basically it will be a hassle to do this for longer lists, but I guess it works. I was hoping for a more fluid process that Excel typically offers (when inputting a formula in one cell and clicking the corner of that cell and dragging that down type of thing), but that does not seem to apply to the online version in this case unless I am missing one or two more things.
Marcin Policht 90,150 Reputation points MVP Volunteer Moderator

2026-02-08T19:13:32.94+00:00
As far as I can tell, what you’re seeing is mostly Excel Online being strict about how Data Validation formulas behave across ranges. When you apply a validation rule to multiple cells, Excel stores one formula and then evaluates it relative to the active cell of the selection. If the reference is fully absolute like $H$15, every row points to the same cell. If you applied the rule starting from a single cell and then tried to extend it later, Excel Online often “freezes” the reference instead of adjusting it. That would explain why it only worked when you manually wrote a different INDIRECT formula for each row.

Clear the validation from your Category cells first. Then select the entire destination range at once, for example I15:I200. With the whole range selected, create one Data Validation rule using this exact formula:

=INDIRECT($H15)

The locked column $H keeps the reference tied to the Type column, while the row stays relative so Excel automatically evaluates it as $H16, $H17, and so on for each row. If you instead use $H$15, every dropdown will always look at H15. If you use H15 without locking the column, copying the validation sideways later can break the reference.

Make sure your named ranges are simple names that exactly match the values in column H, for example ranges named Fruit, Vegetable, and Meat, with no trailing spaces. Excel Online does not reliably accept table structured references inside INDIRECT, so named ranges or direct cell ranges are the safest option.

From what I recall, one extra detail specific to Excel Online is that dragging cells does not copy or adjust Data Validation formulas the same way as normal formulas. The key step is selecting the full target range before creating the validation rule so Excel applies the relative reference correctly from the start.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Elliot 20 Reputation points

2026-02-08T19:55:13.3466667+00:00

Thank you for those tips/explanations! I'll try that out then!
Elliot 20 Reputation points

2026-02-08T20:03:19.3233333+00:00

Hmm, it still doesn't seem to be working. I am still getting an 'error' notification. Man, I'm stumped.
Marcin Policht 90,150 Reputation points MVP Volunteer Moderator

2026-02-09T12:16:17.14+00:00
As far as I can tell, this might be one of Excel Online’s hidden validation rules rather than your logic. The red “error” message you’re seeing usually appears when Excel cannot resolve the formula to a valid range at the moment you click Apply. That means INDIRECT($H15) is evaluating to something that Excel Online does not recognize as an existing named range.

First verify the names actually exist and are workbook-level names. Go to Formulas → Name Manager in the web version and confirm you see names exactly like this with no extra characters:

Fruit Vegetable Meat

Each one must refer directly to a cell range like:

=Sheet1!$A$16:$A$18

If you see anything like a table reference, structured reference, FILTER formula, or a sheet-scoped name, Excel Online will reject it inside Data Validation.

Next test whether INDIRECT itself resolves correctly. In a normal empty cell type:

=INDIRECT(H15)

If that cell shows #REF! or #NAME?, then the problem is definitely the named ranges or extra spaces in column H. Click H15 and retype the value manually rather than using copy/paste because pasted text often carries invisible spaces. You can also test with:

=LEN(H15)

If Fruit shows anything other than 5, you have hidden characters.

Another very common Excel Online issue is that the active cell must be the top-left cell of the selected validation range when you create the rule. Select I15:I200, then click once more on I15 so it becomes the white active cell while the rest stay gray. Then open Data Validation and enter exactly:

=INDIRECT($H15)

If it still errors immediately when you press Apply, try temporarily hardcoding one working name just to confirm Excel Online accepts your ranges at all:

=Fruit

If that also throws an error, then Excel Online is not recognizing your names as valid list sources, which usually means they were created as table column names or sheet-scoped names instead of workbook names.

Another workaround you can try is to create helper cells. In J15 enter:

=INDIRECT($H15)

Copy that down so each row spills the correct list reference. Then set Data Validation source to a fixed helper range like:

=J15#

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Elliot 20 Reputation points

2026-02-10T06:32:06.51+00:00

Okay, so I followed everything written. All my named ranges follow suit with what I would expect per named range. I tried the INDIRECT function by itself and with the $H15 in the function and got the desired list to appear. I did the LEN function and got 5. I did the individual list of Fruit and got the list to appear. I tried putting in the =J15# text in the Data Validation but got an error.
Elliot 20 Reputation points

2026-02-17T04:01:12.5633333+00:00

So, from everything that I've tried on the online version, there isn't an actual way to create a dependent dropdown list? I tried it all several times and used various videos from YouTubers but I am always getting the same result even though I have all of the right names in the name manager with the proper cells selected and type everything exactly the same from one step to the next. Is there really no bug with the INDIRECT function under Data Validation? I still don't understand how I can do the same exact thing on the Excel spreadsheet on my computer version with the INDIRECT and the Data Validation function and it works just fine, but then it's not possible with the online version when every input is the same.
Marcin Policht 90,150 Reputation points MVP Volunteer Moderator

2026-02-17T04:29:11.35+00:00

It appears you’ve actually confirmed the real cause: Excel Online currently does not reliably allow INDIRECT() or spilled range references like J15# inside Data Validation when the rule is applied to multiple rows, even though the formulas themselves work perfectly in normal cells. I gather your options are to either continue with the per-row rule method, temporarily open the file in desktop Excel to create the validation once (it will keep working online afterward), or abandon INDIRECT entirely and switch to a helper-column plus direct range validation structure .

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
Elliot 20 Reputation points

2026-02-21T06:38:31.7933333+00:00

Okay, thank you! It's too bad the online version doesn't allow for this to work the way one would expect. But yes, this has been helpful in clarifying this situation!

Share via

INDIRECT function isn't working for my Dependent Dropdown List

0 additional answers

Your answer