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

OneChief

New Member
Joined
May 5, 2021
Messages
23
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,832
Office Version
  1. 2016
Platform
  1. Windows
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

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

1620254513359.png
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,832
Office Version
  1. 2016
Platform
  1. Windows
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
RSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1PT_ColourAshgrainGlossMattTextureRoughWORK CountWORKworkworkworkworkworkworkworkworkworkworkwork
2AlabasterYY2AshgrainGloss          
3BlackYYY3GlossMattRough         
4RedYY2GlossRough          
5WhiteYYYY4AshgrainMattTextureRough        
6BlueYY2GlossRough          
7             
Colour-Finish
Cell Formulas
RangeFormula
AF2:AF7AF2=IF(AG2="","",COUNTIF(AG2:AR2,"> "))
AG2:AR7AG2=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
ABCDEFGHIJ
1SupDoorThickColourFinishEdgeTHWS
2ACMEBig2BlackGloss3tt567
3ACMESmall4WhiteMatt2ff234
4BodgitMed3BlueRough2dd444
5
6
Selections
Cells with Data Validation
CellAllowCriteria
D2:D4List='Colour-Finish'!$R$2:$R$99
E2:E4List=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

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Does it need to be wrapped with INDIRECT???

Thanks Again.
Chief
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,528
Office Version
  1. 365
Platform
  1. Windows
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
RSTUVWXYZAAABACADAEAF
1PT_ColourAshgrainGlossMattTextureRoughColour
2AlabasterYYAlabasterAshgrainGloss
3BlackYYYBlackGlossMattRough
4RedYYRedGlossRough
5WhiteYYYYWhiteAshgrainMattTextureRough
6BlueYYBlueGlossRough
7 
8 
9 
10 
Colour-Finish
Cell Formulas
RangeFormula
AA2:AA6AA2=Table1[PT_Colour]
AB2:AC2,AB7:AB10,AB6:AC6,AB5:AE5,AB4:AC4,AB3:AD3AB2=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
DE
1ColourFinish
2BlackGloss
3WhiteMatt
4BlueRough
Selections
Cells with Data Validation
CellAllowCriteria
D2:D4List='Colour-Finish'!$AA$2#
E2:E4List=INDEX('Colour-Finish'!$AB$2:$AB$10,MATCH(D2,'Colour-Finish'!$AA$2:$AA$10,0))#


Example showing DV list for colour White

1620276084579.png
 
Last edited:
Solution

OneChief

New Member
Joined
May 5, 2021
Messages
23
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

INDEX('Colour-Finish'!$AJ$2:$AJ$10,MATCH(D2,'Colour-Finish'!$AA$2:$AA$10,0))#
Hello and Thank you.
Is AJ reference correct?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,832
Office Version
  1. 2016
Platform
  1. Windows
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...
 

Forum statistics

Threads
1,137,154
Messages
5,679,917
Members
419,862
Latest member
Bluewings666

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