Dynamic Drop Down List: The Source currently evaluates to an error. Do you want to continue?

Question

I continually try to create a dynamic drop down list using data validation and the indirect function. I keep getting an Error message: The Source currently evaluates to an error. Do you want to continue? I then try to see if the drop down list has worked based on the list chose in the first drop down list and it doesn't work. How can I fix this? I have checked the name manager as well.

Answer 1

Hello Jessica,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

When creating a dynamic dropdown list in Excel using the INDIRECT function, this error message usually means that the reference which INDIRECT is trying to evaluate doesn't correspond to a valid range or a named range. Here's how to troubleshoot and fix this:

1. Ensure that the named ranges you've set up in Name Manager are correctly defined. The names should not have spaces, and the ranges should be correct.
2. In the Data Validation settings, the Source field should refer to a named range or a valid range address. When using INDIRECT, it should look something like this: =INDIRECT(A1) where A1 contains the name of the named range you want to refer to.
3. In a separate cell, test the INDIRECT function by itself to see if it returns the correct range. For example, if you have =INDIRECT(A1) in the Source of your Data Validation and A1 contains the text "MyList", then typing =INDIRECT(A1) in another cell should return the values of the named range "MyList".
4. If the first dropdown list's selection is supposed to dynamically change the second dropdown list, make sure that the first dropdown's value corresponds exactly to the named ranges that are set up for the second dropdown.
5. Any errors in the named ranges, such as #REF! errors, will cause INDIRECT to fail. Ensure that all named ranges refer to existing, correct ranges.
6. Make sure that your INDIRECT does not create a circular reference, where a cell refers back to itself either directly or indirectly.

If you've checked all of the above and the error persists, here's a simple checklist:

• Make sure the named range you're trying to use in INDIRECT exists and is correctly spelled in the Data Validation source.
• The cell you are referring to in INDIRECT must contain the exact name of the named range. For instance, if cell A1 contains "List1", there must be a named range exactly called "List1".
• There should be no extra spaces or characters in the cell that might invalidate the reference.

I hope this helps.

Best Regards, Ibhadighi

Answer 2

It's important to ensure the values in the first list don't have spaces or characters.

When creating a named range, EXCEL replaces spaces etc with underscore ( _ ) but the values in the dropdown still have the spaces. So - then the INDIRECT function doesn't recognise the named range based on the drop-down list where this is the case.

If the original list is one word, or has underscores then the named ranges will match, and the INDIRECT formula will work. This was what was causing the error in my case, so hopefully this helps others.

Answer 3

That depends on what you typed in the Source field for the list. It must be reference (directly or indirectly) to a (named)range somewhere in the workbook, or a list directly typed into the Source field. But it can't be a formula that returns a dynamic array using the FILTER function, for example. Though you may enter such a formula elsewhere, let's say in A1, and then reference the spilled array as A1# in the Source field for the list.