• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation Error

fareedexcel

Member
Hello,

I need to have a dependent drop down based on a list of values. Using the dynamic array formula, the required result is obtained. However, when I tried to use the same formula in the data validation list, the following error appeared.

"The source currently evaluates an error."

The following formula is used to create the drop-down result values:

Code:
=IF(Q7=$V$6,FILTER($V$7:$V$13,$U$7:$U$13=$P7),FILTER($W$7:$W$13,($W$7:$W$13<>"")*($U$7:$U$13=$P7)))

I have attached the sheet for your reference. Kindly help to solve the data validation list issue. I'm not sure what needs to be done in the Name Manager to avoid the error.81069
 

Attachments

  • Dependent Drop Down Help.xlsx
    17.3 KB · Views: 2
In the attached, a clunky solution.
I've made minor alterations to your DropDown named formula and used it in column U. This formula spills horizontally to the right.
I've used the cells in this column as data validation for column J.
Because Tables and dynamic formula don't play together well column U is NOT a table. You will need to copy down column U as far down as table Countries. You could hide column U on another (hidden) sheet if you want to keep things looking tidy.
 

Attachments

  • Chandoo49108Dependent Drop Down Help.xlsx
    16.8 KB · Views: 1
Thanks for this solution. So without a helper column, we wouldn't be able to achieve the result of drop down in the table.
 
I have one problem with the above solution. If I add a blank row within the table, Column U remains blank. Is there any possibility to auto copy the data from the above cells?
 
Thanks, this solution helps. If we insert a blank row within the table, then it's perfect, as data is getting populated in Column U.

There is no option, I guess, if we insert a new row out of the table (by clicking the row numbers). In that case the complete row will be blank and in Column U, the particular row data goes blank
 
Maybe instead of inserting an entire sheet row, insert a row only into the table; hover the mouse over the left hand end of a cell in the first column of the table, it turns into an arrow pointing right:
81072

right-click and choose Insert:
81073

You'll have to experiment with this to see what happens to the formulae in column U and see if they still relate to the correct rows of the table.

I tried to get something into the table itself for DV but haven't succeeded yet.
 
Just now I checked, when I use the below formula and for a single value in Senior Executives, the value is repeated thrice in column U and the same value appears thrice in the drop down too.

=IFERROR(DropDown,U7#)

When I inserted a new blank row within the table, in the column U, the formula is not spilling and it remains blank :(
 
Back
Top