Okay, wanted to stay away from arrayed formulas so it doesn't slow down your sheet.
In Sheet1
In D is a dummy column which is used to pull out the packages for each model car. In D2
Formula:
=IF(B2=B1, "",A2&"_"&COUNTIF($D$1:D1,A2&"*")+1)
In E is concatenation of A and B which is used to pull a range for Color
Formula:
=A2&B2
In F, I created a list of models. In Excel 2007, you can copy Col A to Col F and then from the Data Tab of the ribbon, delete duplicates.
In G I created a list of packages for whatever model is chosen in Sheet2!A2
Formula:
=IFERROR(INDEX($B$2:$B$500,MATCH(Sheet2!$A$2&"_"&ROWS($A$1:A1),$D$2:$D$500,0)),"")
All of these columns can be hidden.
Then I created Dynamic Defined Names (Formula Tab> Name Manager)
Models =
Formula:
=Sheet1!$F$2: (INDEX(Sheet1!$F$2:$F$500, COUNTA(Sheet1!$F$2:$F$500)))
Package =
Formula:
=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$500, SUMPRODUCT(--(LEN(Sheet1!$G$2:$G$500)>0)))
Colour =
Formula:
=INDEX(Sheet1!$C$2:$C$500,MATCH(Sheet2!$A$2&Sheet2!$B$2,Sheet1!$E$2:$E$500,0)):INDEX(Sheet1!$C$2:$C$500, LOOKUP(2, 1/(Sheet1!$E$2:$E$500=Sheet2!$A$2&Sheet2!$B$2),ROW(Sheet1!$C$2:$C$500)-1))
Then set data validation for sheet2 A2,B2,C2 to = models, package, and colour.
See example.
Questions?
Bookmarks