Excel Data entry conditional on previous entry 1

[bilko1]

I am wanting to ensure that specific data is entered into specific fields on specific occasions.
Eg
If Cell A1 entry is "Regal" then cell B1 MUST be either BBB or CCC. Nothing else and it can not be left blank. Any simple ideas ?.
Thanks. I'm having a bad day !

 

[NSoulBri]

The only response I can think of here is to use VBA to set the validation of cell B1 to "BBB" OR "CCC".

If the validation of B1 was NOT dependant on the value of A1 then it would be a simple case of:

Data/Validation... Then settings as follows:

Allow = List
Ignore Blank = True
In-Cell Dropdown = False
Source = BBB,CCC

If you need any help with the VBA solution let me know.

Bri

"Soul music is for life, not just christmas"

 

[xlbo]

Non VBA method
Presumably you already have data validation for your 1st cell (to get regal), so, set aup a table with your required entry lists eg F1 = BBB, F2 = CCC

set up another list which includes all possible entries into your 1st cell and next to them, have the list cell references eg

D1 Regal, E1 F1:F2, F1 BBB, F2 CCC
D2 Not Regal, E2 G1:G2, G1 FFF, G2 GGG
In A2 (with "Regal" in A1), go to data validation , choose "List" and enter:
=INDIRECT(VLOOKUP($A$1,$D$1:$E$2,2,FALSE))

If you pick regal, the data validation list will be F1:F2, if you pick Not Regal, the data validation list will be G1:G2

If this doesn't make any sense, I can email you an example

HTH
Geoff

 

[NSoulBri]

Hats off to xlbo



Your solution to bilko1s' question is far better than mine

You deserve a star Bri

"Soul music is for life, not just christmas"