2 data validation list in 1 cell

alyceinwonderland

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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!
 

Attachments

  • cats.jpg
    cats.jpg
    46.9 KB · Views: 4

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,298
Office Version
  1. 2010
Platform
  1. Windows
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?

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,511
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Try this to see if it is what you want.

21 03 14.xlsm
ABCDE
1CatCheese
2DogDogBone
3MouseApple
One DV
Cells with Data Validation
CellAllowCriteria
A2List=IF(B2="",$D$1:$D$3,"")
B2List=IF(A2="",$E$1:$E$3,"")
 
Solution

alyceinwonderland

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
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?

VBA Code:
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    4.1 KB · Views: 1
  • Capture1.PNG
    Capture1.PNG
    4.7 KB · Views: 1
  • Capture2.PNG
    Capture2.PNG
    4.7 KB · Views: 0

alyceinwonderland

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel board!

Try this to see if it is what you want.

21 03 14.xlsm
ABCDE
1CatCheese
2DogDogBone
3MouseApple
One DV
Cells with Data Validation
CellAllowCriteria
A2List=IF(B2="",$D$1:$D$3,"")
B2List=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,511
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,686
Messages
5,637,822
Members
416,984
Latest member
dee10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top