To solve this error, check all the named ranges and see if you have used the correct named ranges in the formula.
When you try to create a data validation using some formula and you notice an error pop up "The Source currently evaluates to an error. Do you want to continue?". In this article, we will learn why this error pops up and how we can solve it.
Why this error?
The error clearly states that the source to which the formula refers evaluates some error. It means either the formula is incorrect or it refers to some cell or range that returns an error.
Let's give an example. Assuming that we want to create a cascading data validation.
We want excel to show a list of regions in the region cells and in the shop cells, we want to show a list of shops from a selected region.
For regions, we simply select the range for drop down. But to be able to show a list of shops from selected regions only, we will have to use the INDIRECT Function. Before that, you will need to name the ranges that contain shops according to their region.
If you don't name or give reference to B4, we will receive the error notification "The Source currently evaluates to an error. Do you want to continue?"
This is because INDIRECT tries to convert a string into an actual reference. If you give direct reference of a cell that does not contain a name of named range, table of reference string, INDIRECT returns #REF error. This is why we are getting this error.
So first we need to name all the ranges of shops. Once you have named all ranges correctly and have the exact same name in the first drop down list then the error will disappear.
See, now the drop down works fine without any problem.
Other reasons for this error can be formulas or references used in conditional formatting or data validations that refer to other errors. So you need to be careful while using named ranges. You should use exact names of tables and ranges to avoid this error.
I hope this article helped you, if you have any doubts regarding this article or any other topic, ask us in the comments section below. We'll be happy to answer any questions you have.
Related Articles:
Reference Isn't Valid Excel Error and How to Solve It? : This error occurs when we refer to an object or location that doesn't exist or have changed. To solve this problem we track down the reference.
How to Solve "Ambiguous Name Detected" VBA Error : While working with VBA you may get this error popup when you run the procedure. This error says "Ambiguous name detected: procedure name". In this article, we will learn why this error occurs and how we can solve it.
What Application.Screenupdating = False Means and Why is it Used in VBA: Screenupdating is a property of application objects in VBA. It can be set to TRUE or FALSE. It is like a switch, that can be turned On or Off.
"You've Entered Too Few Arguments For This Function" Excel Error and How to Solve It? : You may have observed the "You've Entered Too Few Arguments For This Function" error while using Excel. This happens when you don't provide all the mandatory arguments to the a function in an Excel formula.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to look up value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
In ur example of Region and shop,I am not able to "to name all the ranges of shops." Please try to show it with the help of screenshots.I am getting the same error message "The Source currently evaluates to an error. Do you want to continue?"
Remaining things are working.Please help.