One Cell with multiple Data Validation Lists based on value - read on!

[manc]

Good day Demigods,

Hypothetically,

In Cell A2, depending on what has been chosen in Cell A1, the following four outcomes can be displayed:
Yellow
Blue
Date
Numbers

In Cell B2, the following needs to happen

If Yellow is selected, a drop-down list is populated with data from column A on worksheet 'Yellow'.
If Blue is selected, a drop-down list is populated with data from column A on worksheet 'Blue'.
If Date is selected, it needs to show the date that the user enters into B1, formatted as short date.
If Numbers is selected, it needs to show a number that the user enters into the cell, formatted as number.

Whilst I can get this to work to display two different lists, I can't then freely input any other value into Cell B2, e.g. a date for examples. I have exhausted my research on the "dependants" route, as all the data seems to have to be entered all on one worksheet - The Yellow and Blue worksheets must remain as separate worksheets, as they part of the bigger picture in other areas.

It definitely looks like the VBA route, imho, but "The Legends" of this forum will put me right.

Any ideas, help is greatly appreciated.
Best Regards
manc

 

[shift-del]

Hello

Try this.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1		04.03.2014				Yellow
2	Numbers	48				Blue
3						Date
4						Numbers

<tbody>
</tbody>

Name	Reference
List_Blue	=Blue!$A$1:$A$4
List_Yellow	=Yellow!$A$1:$A$5

<tbody>
</tbody>

Cell	Cond.Format...	Format
B2	1: =B2>40000	yyyy-mm-dd
B2	2: =B2<40000	0

<tbody>
</tbody>

Cell	Validation type	Operator	Value1	Value2
A2	Liste		=$F$1:$F$4
B2	Liste		=CHOOSE(MATCH(A2,F1:F4,0),List_Yellow,List_Blue,B1,B2)

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[manc]

Dear shift-del,
Thank-you for your suggestion. However, the date is not fixed. The user can enter any date they like. The number can also be anything from 0 to 999999999 and may also contain TEXT or special characters.

I am looking for B2 to have the option of List_Yellow, List_Blue, user entered date (not from drop-down) or user entered alpha-numeric entry (not from drop-down).

Best regards
manc

 

[manc]

4 different data validations in one cell? Is it possible? Is it? Is it?

Good morning Demigods,

Based on result of Cell A1, is it possible:

If A1=RED, B1 displays drop-down menu, populated by column A on worksheet RED.
If A1=BLUE, B1 displays drop-down menu, populated by column A on worksheet BLUE.
If A1=DATE, user can enter a date in B1, formatted as dd/mm/yyyy.
If A1=WHATEVER, user can enter any alphanumeric jumble they wish.

I can get "If A1=RED or if A1=BLUE" working correctly, but DATE and WHATEVER, the validation won't let me enter any such values. I would really like all this to happen in Cell B1.

Your help is appreciated,
Best regards
manc

 

[RoryA]

Re: 4 different data validations in one cell? Is it possible? Is it? Is it?

I don't think that is possible solely with data validation - I think you'd need VBA (even then it is impossible to specify dd/mm/yyyy format for a date)

Note: I have merged your two threads together as they appear to be the same question - please do not post duplicate threads on the same topic.

 

[manc]

Re: 4 different data validations in one cell? Is it possible? Is it? Is it?

Sorry boss - Thought my second post was explained better than the first attempt.

Figured VBA might be the answer. Oh well... VBA Demigods are harder to come by.

Best regards
manc

 

[manc]

Re: 4 different data validations in one cell? Is it possible? Is it? Is it?

Could I use something along the following lines?

Sub Button_Select_Departure_Airports_or_Departure_Ports() 'Obviously wouldn't be a button, would need to happen automatically when "'1'!AQ6" is populated'
Dim BR As Integer

BR = Range("'1'!AQ6")

Select Case BR

Case Is < 7: 'Displays drop-down LIST_BLUE'
Case 23 To 28: 'Displays drop-down LIST_RED'
Case 9 To 20: 'Let's user enter a date'
Case 29: 'Let's user enter alphanumeric'

End Select

End Sub


Please put me right
Best regards
manc

 

[RoryA]

Re: 4 different data validations in one cell? Is it possible? Is it? Is it?

Is AQ6 a manual entry or the result of a formula calculation?

 

[manc]

Re: 4 different data validations in one cell? Is it possible? Is it? Is it?

AQ6 is the result of a formula calculation.

I've worked out how to get by for now - all I've done is un-check the "Show error alert after invalid data is entered" box. This kind of takes away the point of having the data validation in the first place, as the idea of the drop-down lists was to ensure users don't make spelling mistakes.