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

 

[OneChief]

@Peter_SSs
Hi Again Peter.

I have a little more complex scenario with the same results required.
Do I need to create separate formulas and tables etc? Or can it be handled in the one table?

I have a data validation list looking at [Supplier] to produce a list for [Door Style]
Then I have a non dependent list to produce 18mm or 21mm [Thickness]

However not all Door Styles come in both thickness's.

I've used your formulas to produce the earlier results.

Which gives the desired results from the table and Availability.

My question is, can I use this for my [Thickness] List, or do I need to create a separate solution?
Secondly, As you can see certain Door Styles also only come in certain finishes. This raises the question how to manage finishes by door style and then by also colour as we have already done? ?

Thanks Mate.
Chief.

 

[OneChief]

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...

Thank you for your help and suggestions. ?

 

[Peter_SSs]

I have a little more complex scenario ...

Yes, it is getting very complex and I am not at all familiar with your data, layout etc so I am struggling to understand.

Are you able to make up a small set of dummy data that demonstrates what you have and what you want and post that with XL2BB so that I can copy what you have in that sample (no way tying out manually all you have in those images is going to happen )> Then explain precisely as you can in relation to that sample data what you want and where.