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

OneChief

New Member
Joined
May 5, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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)
1620250683418.png

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)
1620250852108.png

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

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

New Member
Joined
May 5, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
@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]
1620351868715.png

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

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


1620352393934.png

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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

OneChief

New Member
Joined
May 5, 2021
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,157
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,587
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top