2 data validation list in 1 cell

[alyceinwonderland]

Hi,

I'm currently stuck on my formula for this project. So, I have to cells that both already have a drop down list. What I want to happen is when you choose from the drop down of Cell A, Cell B will be disable. For example:

in Cell A the list is: Cat, Dog, Mouse
in Cell B the list is: Cheese, Bone, Apple

If you choose "Cat" in cell A then I want cell B to be disabled or will not allow you to choose from the list.

I tried this data validation formula on cell B but I get the error message "the list source must be a delimited list or reference to single row or column":

=OR($A$2="Cat",COUNTIF($A$2,"")='FINAL PARAMETER'!$B$2:$B$4)

Is there another formula that I can do so that when you choose a certain item on the list the other list will be disabled?

Thank you and have a great day!

 

[diddi]

hi and welcome to MrExcel
there are probably other ways to do this, but i would assign a macro to the dropdown in A. the macro can then disable the other dropdown.
and while you are thinking about it, how do you get to show dropdown B again?

Sub DisableDropB()
    Sheets("Sheet1").DropDowns(2).Enabled = False
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this to see if it is what you want.

21 03 14.xlsm
	A	B	C	D	E
1				Cat	Cheese
2	Dog			Dog	Bone
3				Mouse	Apple
One DV

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=IF(B2="",$D$1:$D$3,"")
B2	List	=IF(A2="",$E$1:$E$3,"")

 

[alyceinwonderland]

hi and welcome to MrExcel
there are probably other ways to do this, but i would assign a macro to the dropdown in A. the macro can then disable the other dropdown.
and while you are thinking about it, how do you get to show dropdown B again?

Sub DisableDropB()
    Sheets("Sheet1").DropDowns(2).Enabled = False
End Sub

Thank you for your reply Diddi.

How do I put the code in the excel file? I'm actually an amateur when it comes to excel.

I was thinking that when they choose the other options aside from "cat" then drop down B should be enabled.
Kindly see attached photos for reference.

As you can see in the photo I change the inputs. So, when the Purpose is Payment then the Classification should be disabled. How do I do that when there is a data validation in each cell provided. I'm also not familiar with macro but I will do research on that.

Thank you.

 

[alyceinwonderland]

Welcome to the MrExcel board!

Try this to see if it is what you want.

21 03 14.xlsm
	A	B	C	D	E
1				Cat	Cheese
2	Dog			Dog	Bone
3				Mouse	Apple
One DV

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=IF(B2="",$D$1:$D$3,"")
B2	List	=IF(A2="",$E$1:$E$3,"")

Hi Peter_SSs

Thank you for your reply.

I tried what you provided but it did not work out. It returned the same error "the list source must be a delimited list or reference to single row or column".

 

[Peter_SSs]

I tried what you provided but it did not work out. It returned the same error "the list source must be a delimited list or reference to single row or column".

Did you put the lists in the worksheet like I did in columns D & E (you could hide those columns) and did you use those ranges in the Data Validation formula like I did or did you try to type the words like "Cat", "Dog" etc directly into the formula?

I suggest that for a test you forget your actual sheet and values and try in a fresh worksheet and set it up exactly like my sample above & see what happens.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: 2 data validation in 1 cell
and how_to_have_two_data_validation_in_one_cell
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.