Hello Guys, I am stuck with Dependent data validation! I am using named range like animal in A1 and then in B1 there is indirect A1 which brings the list for validation. The problem is if A1 is empty b1 accepts any value !! and Data Validations list option in not accepting formula with named ranges as
if(A1="Animal",Animal,if(A1="Number",Number..... so on
For more clarification workbook is attached.
Regards
Hemesh
Last edited by hemesh; 09-17-2013 at 08:54 AM.
Hi,
Try unchecking the Ignore Blank option.
Regards
Hi, That worked and One more thing how to add if formula in data validation with named ranges it will constantly show the list source must be delimited
Last edited by hemesh; 09-17-2013 at 10:04 AM.
I'm sorry. I don't understand your request. Could you perhaps re-attach your workbook with an illustrated example?
Regards
Sorry For delayed reply but XOR what i am trying to do is to apply a formula in DV list option as =if(A1="Numbers",Numbers,if(A1="Alphabet",Alphabet and so on to create drop down instead of using =indirect(a1) but i am getting error in that part.
attached is the example file
Hi,
But what precisely is wrong with the current INDIRECT method you are using? What would be the advantage you see in this alternative method?
Regards
I am simultaneously working on other project where I am trying to use if function using indirect with named range is not a issue at all but I wanted to know why i am not able to use if function with named ranged and where I am going wrong
You must be sure that the names in your formula are the same as your named ranges. numbers, not number, for example
Hello I have useed original named ranges but still there is a error
What is the error message?
List source must be delimited
What formula did you enter?
Have you checked the attachment? it is updated in that
Try to put =If (a1="alphabets", alphabets) and see what error message
I see now - you must put a cell reference for the Else part of your formula
Last edited by Izandol; 09-18-2013 at 08:17 AM.
Izandol have you gone throgh the thread properly.
I trying to create a formula for list in DV with if function
No, he has a point:
"Try to put =If (a1="alphabets", alphabets) and see what error message."
You have not specified an ELSE clause in this formula. More importantly, you do not have a Defined Name called alphabets - though I can see one called alphabet.
Try this if you wish to follow that route, though it's a painfully inefficient way:
=IF(A1="alphabet",alphabet,IF(A1="animals",animals,numbers))
Regards
Got it in example file I used wrong names and in my original file named ranges are correct but a1 contains extra spaces
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks