Data validation for drop down

[layalhasan]

I have a excel sheet and i have created a data validation function. Its a conditional dropdown but I can not input my equation in data validation field. Its says too many arguments. My equation is

[FONT=Arial]=IF(A4="Capital",'New List'!$C$2:$C$42,IF(A4="Pier Caps",'New List'!$C$43:$C$84),IF(A4="Keystones",'New List'!$C$85:$C$86),IF(A4="Round Louvre",'New List'!$C$87:$C$88),IF(A4="Open Rings",'New List'!$C$89:$C$90),IF(A4="Pyramids",'New List'!$C$91:$C$92),IF(A4="Plinths",'New List'!$C$93:$C$94),IF(A4="Columns",'New List'!$C$95:$C$96))[/FONT]

What i am missing here? Is it too long? Is there any other way to achieve this? I have some more argument which i need to add with this function. Please help

 

[JoeMo]

I have a excel sheet and i have created a data validation function. Its a conditional dropdown but I can not input my equation in data validation field. Its says too many arguments. My equation is

[FONT=Arial]=IF(A4="Capital",'New List'!$C$2:$C$42,IF(A4="Pier Caps",'New List'!$C$43:$C$84),IF(A4="Keystones",'New List'!$C$85:$C$86),IF(A4="Round Louvre",'New List'!$C$87:$C$88),IF(A4="Open Rings",'New List'!$C$89:$C$90),IF(A4="Pyramids",'New List'!$C$91:$C$92),IF(A4="Plinths",'New List'!$C$93:$C$94),IF(A4="Columns",'New List'!$C$95:$C$96))[/FONT]

What i am missing here? Is it too long? Is there any other way to achieve this? I have some more argument which i need to add with this function. Please help

First, if you want to use a custom function it must evaluate to TRUE or FALSE and it will not produce a dropdown.

To produce a dropdown using the value in A4 you can do this.
1. Create named ranges like: Capital, Pier_Caps, Keystones, ...... with your list for each name.
2. Select the cell you want the dropdown in.
3. In the Data validation dialog box select 'List' and in the formula box type: = INDIRECT(A4)
Now if you enter 'Capital' in A4 the Data Validation drop down will show the list for the named range 'Capital'.

 

[Teeroy]

JoeMo is correct for the best way to get a conditional list for data validation but you need to keep in mind that if the input value in A4 doesn't match a Name then probably no entry will be accepted in the validated cell and you won't get the dropdown.

Also you haven't embedded the if statements correctly to evaluate the formula you originally posted. The next if condition needs to be in the third term of the if statement above it e.g.

=IF(A1="City", "True1", IF(A1="Country", "True2", "ultimate fail"))

 

[layalhasan]

First, if you want to use a custom function it must evaluate to TRUE or FALSE and it will not produce a dropdown.

To produce a dropdown using the value in A4 you can do this.
1. Create named ranges like: Capital, Pier_Caps, Keystones, ...... with your list for each name.
2. Select the cell you want the dropdown in.
3. In the Data validation dialog box select 'List' and in the formula box type: = INDIRECT(A4)
Now if you enter 'Capital' in A4 the Data Validation drop down will show the list for the named range 'Capital'.

Thanks for your quick reply. I followed your idea and created table and assigned name. Then I used =INDIRECT() function. It works.

But one little problem. Name with two words won't work.

 

[layalhasan]

JoeMo is correct for the best way to get a conditional list for data validation but you need to keep in mind that if the input value in A4 doesn't match a Name then probably no entry will be accepted in the validated cell and you won't get the dropdown.

Also you haven't embedded the if statements correctly to evaluate the formula you originally posted. The next if condition needs to be in the third term of the if statement above it e.g.

=IF(A1="City", "True1", IF(A1="Country", "True2", "ultimate fail"))

Thanks for reply. You are right by the way. For "Piers Cap", data validation won't work because I had to name that range to "Piers_Caps". Any other way to fix this issue?

 

[Teeroy]

Thanks for reply. You are right by the way. For "Piers Cap", data validation won't work because I had to name that range to "Piers_Caps". Any other way to fix this issue?

Unfortunately not; spaces are not allowed in a named range. Have a look at This Link for the rules.

 

[layalhasan]

Thanks All for your help. I will change those names.