Validation list based on value of the cell

[thewiseguy]

Hi all,

I have tried to search for a remedy, to no avail. Hoping someone can help.

I have a dedicated worksheet for manual input of data. (see example table below).

ECM Group	NJSS Fix/Lamp Desc.	EMA DESCRIPTION	DESCRIPTION	ECM Type	CUSTOMER DESCRIPTION	MFG	PART NO.	NO. LAMPS	LAMP WATTS
TwoFootLamps	LED Tube – 2' Linear Replacement Lamps	MAXLITE, 1 Lamp, 2FT, 9W, 35K, 1125 lumen, LED Type B, Bypass Linear T8 Tube	1 Lamp, 2FT, 9W, 35K, 1125 lumen, LED Type B, Bypass Linear T8 Tube	Tube	1L 9W 2FT LED 35K	MAXLITE	L9T8DE235-CG4	1	9
TwoFootLamps	LED Tube – 2' Linear Replacement Lamps	MAXLITE, 1 Lamp, 2FT, 9W, 4K, 1200 lumen, LED Type B, Bypass Linear T8 Tube	1 Lamp, 2FT, 9W, 4K, 1200 lumen, LED Type B, Bypass Linear T8 Tube	Tube	1L 9W 2FT LED 4K	MAXLITE	L9T8DE240-CG4	1	9
ThreeFootLamps	LED Tube – 3' Linear Replacement Lamps	MAXLITE, 1 Lamp, 3FT, 12W, 35K, 1225 lumen, LED Type B, Bypass Linear T8 Tube	1 Lamp, 3FT, 12W, 35K, 1225 lumen, LED Type B, Bypass Linear T8 Tube	Tube	1L 12W 3FT LED 35K	MAXLITE	L12T8DE335-CG4	1	12
ThreeFootLamps	LED Tube – 3' Linear Replacement Lamps	MAXLITE, 1 Lamp, 3FT, 12W, 4K, 1225 lumen, LED Type B, Bypass Linear T8 Tube	1 Lamp, 3FT, 12W, 4K, 1225 lumen, LED Type B, Bypass Linear T8 Tube	Tube	1L 12W 3FT LED 4K	MAXLITE	L12T8DE340-CG4	1	12
FourFootLamps	LED Tube – 4' Linear Replacement Lamps	EIKO, 1 Lamp, 4FT, 25W, 5K, 3500 lumen, LED Type B, Bypass Linear T5 Tube	1 Lamp, 4FT, 25W, 5K, 3500 lumen, LED Type B, Bypass Linear T5 Tube	Tube	1L 25W 4FT T5 LED 5K	EIKO	LED25WT5HO/46/850-G8D	1	25
FourFootLamps	LED Tube – 4' Linear Replacement Lamps	EIKO, 2 Lamp, 4FT, 25W, 5K, 3500 lumen, LED Type B, Bypass Linear T5 Tube	2 Lamp, 4FT, 25W, 5K, 3500 lumen, LED Type B, Bypass Linear T5 Tube	Tube	2L 25W 4FT T5 LED 5K	EIKO	LED25WT5HO/46/850-G8D	2	25

On a separate worksheet, I have a column (column A) which is dedicated to choosing from a validation list. This list represents "ECM Group," in the table above.

Once this cell is selected and a value from the list is chosen, I would like column, "EMA Description," in the table above to available for selection from a list.

So if I choose, "ThreeFootLamps," then: (both of these below, would be available for selection in the next cell)

MAXLITE, 1 Lamp, 3FT, 12W, 35K, 1225 lumen, LED Type B, Bypass Linear T8 Tube
MAXLITE, 1 Lamp, 3FT, 12W, 4K, 1225 lumen, LED Type B, Bypass Linear T8 Tube

Here is where I run into trouble - I would like to do this WITHOUT creating additional lists. I can make this work if i create multiple tables within this table, but I would like to keep 1 large table with all of the data.

Is this possible?

Hope this is clear - thank you in advance.


/M

 

[Ron Coderre]

You're looking to build Dependent DropdownLists.
Jon Acampora covers them nicely at his ExcelCampus website.

How to Create Dependent Drop-down Lists | Excel Campus

Learn how to create dependent drop-down lists (also known as cascading validation) in Excel. This technique does NOT require named ranges. Free download.

www.excelcampus.com

Does that help?

 

[thewiseguy]

Ron - this looks to be what I need. Many thanks...will be working on this now.

 

[Akuini]

If you're ok to use vba, here's an example of 2 dependent data validation using vba no formula.

dhee - 2 data validation, unique, dependent, fixed range.xlsm

Shared with Dropbox

www.dropbox.com