Look up a table, check availability then create a drop down list.

[OneChief]

Hi Everyone,
I have some tables that show information and availability of products. e.g., List of colours then columns that show what finishes they are available in. (Pic 01)

Firstly I am not sure if I am doing this in the best practice, but this is what I have so far.
I then have another table with data validation generating drop down lists which are dependent on other cells. (Pic 02)

Currently I have had to create individual tables named as the colours with a list of finishes under them. (Pic 03)

This creates a lot of work and not very user friendly when updating or adding more colours.
Is there a way I can look up the list of colours in the first table that hold the colours vertically, and the Finishes horizontally, then produce a data validation list based on if the finish has a Y?
Look up the colour, gather all the Y, then generate the drop down list.

Any suggestions and recommendations are welcome.

Thank you

 

[Toadstool]

Hi OneChief,

Is the "Cafe Cream" a Pt_Colour_T we don't see in picture 1 and an entry we don't see in row 1 of picture 3?

What is the maximum number of Finishes you'll ever have (because I'll need to appropriate that many columns to the right).

 

[OneChief]

Hi OneChief,

Is the "Cafe Cream" a Pt_Colour_T we don't see in picture 1 and an entry we don't see in row 1 of picture 3?

What is the maximum number of Finishes you'll ever have (because I'll need to appropriate that many columns to the right).

G'Day mate,
Yes "Cafe Cream" is in the column Pt_Colour_T. and Yes to that as well.

 

[Toadstool]

You didn't say maximum finishes so I've assumed 12. I'm allowing for up to 99 colours.

I'm building the LoV to the right of your Colour/Finish matrix and those 12 columns of formulae should be copied down the 99 rows.

The LoV for Finish then becomes an OFFSET to the built LoV.

Here your Colour/Finish matrix sheet:

OneChief.xlsx
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1	PT_Colour	Ashgrain	Gloss	Matt	Texture	Rough									WORK Count	WORK	work	work	work	work	work	work	work	work	work	work	work
2	Alabaster	Y	Y												2	Ashgrain	Gloss
3	Black		Y	Y		Y									3	Gloss	Matt	Rough
4	Red		Y			Y									2	Gloss	Rough
5	White	Y		Y	Y	Y									4	Ashgrain	Matt	Texture	Rough
6	Blue		Y			Y									2	Gloss	Rough
7
Colour-Finish

Cell Formulas
Range	Formula
AF2:AF7	AF2	=IF(AG2="","",COUNTIF(AG2:AR2,"> "))
AG2:AR7	AG2	=IFERROR(INDEX($S$1:$AC$1,AGGREGATE(15,6,COLUMN($S$2:$AC$2)-COLUMN($R$2)/($S2:$AC2="Y"),COLUMN()-COLUMN($AF$2))),"")

Here's your Selections sheet

OneChief.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Sup	Door	Thick	Colour	Finish	Edge	T	H	W	S
2	ACME	Big	2	Black	Gloss	3	tt	5	6	7
3	ACME	Small	4	White	Matt	2	ff	2	3	4
4	Bodgit	Med	3	Blue	Rough	2	dd	4	4	4
5
6
Selections

Cells with Data Validation
Cell	Allow	Criteria
D2:D4	List	='Colour-Finish'!$R$2:$R$99
E2:E4	List	=OFFSET('Colour-Finish'!$AG$1,MATCH(D2,'Colour-Finish'!$R$2:$R$99,0),0,1,INDEX('Colour-Finish'!$AF$2:$AF$99,MATCH(D2,'Colour-Finish'!$R$2:$R$99,0)))

 

[OneChief]

Thanks Mate,

I've worked thru your solution and all appears to be working great.

Looks like the only issue I have is in the Data Validation. When I enter the formula I get an error.

Using the formula in a cell works and spills out, but does not work in the Data Validation list.

Does it need to be wrapped with INDIRECT???

Thanks Again.
Chief

 

[Peter_SSs]

Since you have Excel 365, you should be able to do it with a little less work. See if something like this is any use.

Formula in AA2 will automatically spill the other colours down the column and will auto-expand/contract if the number of colours in the table changes.
Formula in AB2 (table name needs to be adjusted in this formula to match your table details) needs to be copied down as far as you might ever need (ie to max number of colours you would ever get, but I have used just to row 10) but it does not need to be copied across any columns to the right as those results again will automatically spill across as many columns as required.

OneChief.xlsm
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	PT_Colour	Ashgrain	Gloss	Matt	Texture	Rough				Colour
2	Alabaster	Y	Y							Alabaster	Ashgrain	Gloss
3	Black		Y	Y		Y				Black	Gloss	Matt	Rough
4	Red		Y			Y				Red	Gloss	Rough
5	White	Y		Y	Y	Y				White	Ashgrain	Matt	Texture	Rough
6	Blue		Y			Y				Blue	Gloss	Rough
7
8
9
10
Colour-Finish

Cell Formulas
Range	Formula
AA2:AA6	AA2	=Table1[PT_Colour]
AB2:AC2,AB7:AB10,AB6:AC6,AB5:AE5,AB4:AC4,AB3:AD3	AB2	=IF(AA2="","",FILTER(Table1[#Headers],INDEX(Table1,MATCH(AA2,Table1[PT_Colour],0),0)="Y",""))
Dynamic array formulas.

Then for the selections, you can use these Data Validations.

OneChief.xlsm
	D	E
1	Colour	Finish
2	Black	Gloss
3	White	Matt
4	Blue	Rough
Selections

Cells with Data Validation
Cell	Allow	Criteria
D2:D4	List	='Colour-Finish'!$AA$2#
E2:E4	List	=INDEX('Colour-Finish'!$AB$2:$AB$10,MATCH(D2,'Colour-Finish'!$AA$2:$AA$10,0))#

Example showing DV list for colour White

 

[OneChief]

INDEX('Colour-Finish'!$AJ$2:$AJ$10,MATCH(D2,'Colour-Finish'!$AA$2:$AA$10,0))#

Hello and Thank you.
Is AJ reference correct?

 

[Peter_SSs]

Is AJ reference correct?

No, sorry, that was a test range that I was using and forgot to update that part. I have now corrected it in my previous post.

 

[Toadstool]

Looks like the only issue I have is in the Data Validation. When I enter the formula I get an error.

Yes, it won't work if Colour-Finish is a table, but it looks like Peter has supplied a solution better suited to your Excel version so I'll step away...

 

[OneChief]

Thank you Both..
Works really well.. ?