Data Validation - Allow Any Value

[mrbougles]

Hi,

I wish to validate a cell (lets say A1) as below.

- If B1 = "cat" then allow dropdown List 1
- If B1 = "dog" then allow dropdown List 2
- If B1 = "fish" then allow free text / anything

Any ideas?

Help appreciated. Apologies if an answer has already been posted but i couldnt find it.

Cheers,

 

[MARK858]

AFAIK you can't have multiple dropdowns in the same cell but you can do dependent drop down lists. If that is what you mean then please see the tutorial in the link below.

http://www.contextures.com/xlDataVal02.html

 

[NMa91]

Create three Lists Namely Cat, Dog, Fish. Also, create name called 'List' refers to =INDIRECT(Sheet1!$B$1,TRUE)
In Cell A1, add the validation. Allow List and source : =List.
Enter Cat or Dog or Fish in cell B1, validation is applied as per the selection.

 

[mrbougles]

That's me logged off excel for the night but will give these a go tomorrow.

Mark 858 - I think you can have dependant dropdowns as I have this already. Could be mistaken / misunderstanding though.

 

[mrbougles]

Create three Lists Namely Cat, Dog, Fish. Also, create name called 'List' refers to =INDIRECT(Sheet1!$B$1,TRUE)
In Cell A1, add the validation. Allow List and source : =List.
Enter Cat or Dog or Fish in cell B1, validation is applied as per the selection.

This seems to work. Thak you.
As long as the "fish" list is empty (basically a fake list) which is fine.
Not sure why it allows free text but I'm happy with the behaviour. I will now apply this to my use case.

Previously i nearly had a solution nearly working with various IF statement formulas along the lines of =if(B1="cat",catlist,if(B1="dog",doglist,"*"))
but I couldnt get it working 100%. I thought that "*" wildcard could be used to permit free text but i've had mixed results with this.

Thank you.