• 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 list on a data validation list is not working

D

DashboardNovice

Member
In column A, the user selects from a list of names. In column B, I want the data validation list source to reference the value in column A, which is a named range.

So column B, references a named range in column A. When the user clicks the in cell drop down arrow in column B, different choices will appear in the list, based on the name selected in column A.

In column B, my data validation list formula is:

Code: 
=indirect($A4)

This evaluations to an error. The current value in column A is definitely a named range.

What could be going wrong?
 
Ok, my problem was that my named ranges had a ", " and a in some cases an additional " " where some people have their middle name listed.

So I used the SUBSTITUTE function to find and replace ", " with "__". But then to take care of those instances where a person has a middle name I had to do a SUBSTITUTE on the first SUBSTITUTE, which works fine. But then when I apply the INDIRECT function it evaluates to a VALUE! error.

Why is this?
 
You must log in or register to reply here.
Back
Top