Data Validation : Drop Down List with a Formula

[jbhakta]

Hi
I wanted to find out which is the efficient way to have a drop down data validation list which changes according to the entry in the table below.
I have tried IF formula but not sure if efficient as a have a lot of conditions.

Brands

Nike

Puma

Adidas

Vega

Geox

New Balance

OTHER

******

◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦

◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦

◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦

◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦

◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦

◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦

◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦

B5

Nike

SKU0001

SKU0100

SKU0200

SKU0300

SKU0400

SKU0500

SKU0600

B6

Puma

SKU0002

SKU0101

SKU0201

SKU0301

SKU0401

SKU0501

SKU0601

B7

Adidas

SKU0003

SKU0102

SKU0202

SKU0302

SKU0402

SKU0502

SKU0602

B8

Vega

SKU0004

SKU0103

SKU0203

SKU0303

SKU0403

SKU0503

SKU0603

B9

Geox

SKU0005

SKU0104

SKU0204

SKU0304

SKU0404

SKU0504

SKU0604

B10

New Balance

SKU0006

SKU0105

SKU0205

SKU0305

SKU0405

SKU0505

SKU0605

B11

OTHER

SKU0007

SKU0106

SKU0206

SKU0306

SKU0406

SKU0506

SKU0606

B12

SKU0008

SKU0107

SKU0207

SKU0307

SKU0407

SKU0507

SKU0607

B13

B14

Nike is in Cell B5

I have brands then depending on the Brands selected appropriate drop down table appears in cell C19 (currently selected SKU0601 below)

B16	Brand	Department
B17
B18	Select	Select
B19	OTHER	SKU0601	Need Formula in the data validation that would bring the relevant drop down list from above table
		C19	I tried IF formula but ran out of space and would not let me extend the formula
			=IF($B$19=$B$5,$D$5:$D$14,IF($B$19=$B$6,$F$5:$F$14,IF($B$19=$B$7,$H$5:$H$14,IF($B$19=$B$8,$J$5:$J$14,IF($B$19=$B$9,$L$5:$L$14,IF($B$19=$B$10,$N$5:$N$14,IF($B$19=$B$11,$P$5:$P$14)))))))

I have inserted above formula in the Data Validation List Formula but what I am finding on other sheets is that the formula is not accepted in the data validation when I have many brands...
Is there a more efficient way to do this?

Above formula works here but when I have a sheet with twenty brands the formula is too long.

Let me know if anyone has a suggestion...

I have thought of another way using Hlookup but wondered if anyone else knew other ways

Thanks
Jay

 

[Fluff]

If you create named ranges for each of your lists you can do it like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		Brands		Nike		Puma		Adidas		Vega		Geox		New Balance		OTHER
4		******		◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦		◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦		◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦		◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦		◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦		◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦		◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦◦
5		Nike		SKU0001		SKU0100		SKU0200		SKU0300		SKU0400		SKU0500		SKU0600
6		Puma		SKU0002		SKU0101		SKU0201		SKU0301		SKU0401		SKU0501		SKU0601
7		Adidas		SKU0003		SKU0102		SKU0202		SKU0302		SKU0402		SKU0502		SKU0602
8		Vega		SKU0004		SKU0103		SKU0203		SKU0303		SKU0403		SKU0503		SKU0603
9		Geox		SKU0005		SKU0104		SKU0204		SKU0304		SKU0404		SKU0504		SKU0604
10		New Balance		SKU0006		SKU0105		SKU0205		SKU0305		SKU0405		SKU0505		SKU0605
11		OTHER		SKU0007		SKU0106		SKU0206		SKU0306		SKU0406		SKU0506		SKU0606
12				SKU0008		SKU0107		SKU0207		SKU0307		SKU0407		SKU0507		SKU0607
13
14
15
16
17
18
19		New Balance	SKU0501
20
Main

Cells with Data Validation
Cell	Allow	Criteria
B19	List	=Brands
C19	List	=INDIRECT(SUBSTITUTE(B19," ","_"))

 

[jbhakta]

Hi

I dont understand what you mean by =INDIRECT(SUBSTITUTE(B19," ","_"))

Are you able to explain the formula.

Jay

 

[Fluff]

You need to create named ranges called Nike, Puma Etc However for New Balance it needs to be named New_Balance as you cannot have spaces in a named range.
The formula looks at the value in B19 & returns the relevant named range substituting any spaces with an underscore.

 

[Peter_SSs]

@jbhakta
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jbhakta]

My version in 9 years old... Excel 97 _2003

By the way I don't have space... above New Balance was just an example... so do not need the Substitute formula.

How does the INDIRECT formula work? Do I put it in the Data Validation box.

I have never used INDIRECT formula before... I'm reading up on it now but feeling lost.

Do I need to first create a table with Brands and Range name next to it.. so to use in the INDIRECT formula in my Data Validation...

This is my name range table:

Brands	Range Name
Nike	NK1
Puma	PM1
Adidas	AD1
Vega	AG1
Geox	GO1

Thanks
Jay

 

[Fluff]

The named ranges need to include the data you want in the drop-down, so in the example from post#2 Brands would be B5:B11, Nike would be D5:D12 etc.

Do I put it in the Data Validation box

Yes, select List & then put the formula in the source box. If all your named ranges are single words then you can use

=INDIRECT(B19)

 

[jbhakta]

Thank you
I think I have it now.

Jay

 

[Fluff]

You're welcome & thanks for the feedback.