Selecting cells from another sheet based on dropdown lists

[nofam]

Hi All,

Pretty much at wits' end here.

I have a list of 12000 products in one sheet which are categorized in a hierarchy as follows:

Department
Sub-group
SKU

Then description, price etc:

<table style="border-collapse: collapse; width: 774pt;" x:str="" width="1031" border="0" cellpadding="0" cellspacing="0"><col style="width: 186pt;" width="248"> <col style="width: 226pt;" width="301"> <col style="width: 37pt;" width="49"> <col style="width: 262pt;" width="349"> <col style="width: 63pt;" width="84"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 186pt;" class="xl23" width="248" height="17">Department</td> <td style="width: 226pt;" class="xl23" width="301">Sub-group</td> <td style="width: 37pt;" class="xl23" width="49">SKU</td> <td style="width: 262pt;" class="xl23" width="349"> Matrix_Description</td> <td style="width: 63pt;" class="xl23" width="84">Large Rates</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CEILING TILES</td> <td>438101
</td> <td> TILE CEILING FRESCO 600X400X13MM</td> <td x:num="4.1921999999999997" align="right">4.1922</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CEILING TILES</td> <td>438102</td> <td> TILE CEILING STUCCO 600X400X13MM</td> <td x:num="4.1921999999999997" align="right">4.1922</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CEILING TILES</td> <td>438104</td> <td> TILE CEILING PLAIN 600X400X13MM</td> <td x:num="4.1921999999999997" align="right">4.1922</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CEILING TILES</td> <td>438105</td> <td> TILE CEILING WEAVE 600X400</td> <td x:num="4.1921999999999997" align="right">4.1922</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CEILING TILES</td> <td>438106</td> <td>TILE CEILING CRATER 600X400X13MM</td> <td x:num="4.1921999999999997" align="right">4.1922</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CONCRETE PRODUCTS</td> <td>371682</td> <td>BITUPROOF PLUS 20LT BITUMINOUS PAINT</td> <td x:num="117.61920000000001" align="right">117.6192</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CONCRETE PRODUCTS</td> <td>431001</td> <td>CEMENT PORTLAND 40KG</td> <td x:num="14.259499999999999" align="right">14.2595</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CONCRETE PRODUCTS</td> <td>431123</td> <td>BLACKSEAL PLUS 20L</td> <td x:num="" align="right">163.2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CONCRETE PRODUCTS</td> <td>431125</td> <td>PAVE SET 30KG</td> <td x:num="28.209599999999998" align="right">28.2096</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BUILDING SUPPLIES</td> <td>CONCRETE PRODUCTS</td> <td>431127</td> <td>BUILDERS MIX BAGGED RESULTS 25L</td> <td x:num="6.282" align="right">6.282</td> </tr> </tbody></table>

I've set up dependent validation in a second sheet so that if I pick BUILDING SUPPLIES in the first list, I only see CEILING TILES or CONCRETE PRODUCTS etc etc in the second.

What's got me stumped is how I can use the resulting two criteria to search the list of products back on the first sheet, and display them where I want on the second sheet:

<table style="border-collapse: collapse; width: 379pt;" x:str="" width="504" border="0" cellpadding="0" cellspacing="0"><col style="width: 80pt;" width="106"> <col style="width: 230pt;" width="306"> <col style="width: 69pt;" width="92"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 80pt;" valign="top" width="106" align="left" height="17"> <table cellpadding="0" cellspacing="0"> <tbody><tr> <td width="0" height="0">
</td> <td width="105">
</td> <td width="1">
</td> </tr> <tr> <td height="25">
</td> <td colspan="2" valign="top" align="left">
</td> </tr> <tr> <td height="8">
</td> </tr> <tr> <td height="25">
</td> <td valign="top" align="left">
</td> </tr> </tbody></table> <table cellpadding="0" cellspacing="0"> <tbody><tr> <td style="height: 12.75pt; width: 80pt;" width="106" height="17">
</td> </tr> </tbody></table> </td> <td style="width: 230pt;" width="306">
</td> <td style="width: 69pt;" class="xl25" width="92">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl26"><dropdown1> BUILDING SUPPLIES</dropdown1></td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl26"><dropdown2> CEILING TILES</dropdown2></td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" class="xl26" height="17">SKU</td> <td style="border-left: medium none;" class="xl26">Desc</td> <td x:str="Large Rates" style="border-left: medium none;" class="xl27"> Large Rates </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">438101</td> <td>TILE CEILING FRESCO 600X400X13MM</td> <td x:num="4.1921999999999997" class="xl25"> $ 4.19 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">438102</td> <td>TILE CEILING STUCCO 600X400X13MM</td> <td x:num="4.1921999999999997" class="xl25"> $ 4.19 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">438104</td> <td>TILE CEILING PLAIN 600X400X13MM</td> <td x:num="4.1921999999999997" class="xl25"> $ 4.19 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">438105</td> <td>TILE CEILING WEAVE 600X400</td> <td x:num="4.1921999999999997" class="xl25"> $ 4.19 </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">438106</td> <td>TILE CEILING CRATER 600X400X13MM</td> <td x:num="4.1921999999999997" class="xl25"> $ 4.19</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td>
</td><td x:num="117.61920000000001" class="xl25">
</td> </tr> </tbody></table>

given that the results may be 5 products, or 500?

I've tried using VLOOKUP, but can't figure out how to base it on more than one criteria?

Please help!!

 

[nofam]

Cheers Tom - I don't actually need the Message Box however; is there just a way to do this by selecting the required Department, and then just (ALL) from the Sub-group list?

 

[Tom Urtis]

Yes, that would be simple just have cell C12 be the only target cell of interest for the Change event. The problem is, if you do that, how will you ever ask the user to differentiate what they want - - just the Department or the Department and Subgroup. It's a logic issue you'd need to consider and then implement the code based on that logic.

 

[nofam]

I ended up doing the following Tom:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C12,C14")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) = True Then Exit Sub
Dim strDepartment$, strSubGroup$
strDepartment = Range("C12").Value
strSubGroup = Range("C14").Value

Application.ScreenUpdating = False

Select Case strSubGroup

Case "(ALL)"



Rows("20:" & Rows.Count).Clear
With Sheets("Sheet2")
.AutoFilterMode = False
With .Range("match")
.AutoFilter Field:=1, Criteria1:=strDepartment
On Error Resume Next
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Copy Range("A20")
Err.Clear
End With
.AutoFilterMode = False
End With


Case Else


Application.ScreenUpdating = False
Rows("20:" & Rows.Count).Clear
With Sheets("Sheet2")
.AutoFilterMode = False
With .Range("match")
.AutoFilter Field:=1, Criteria1:=strDepartment
.AutoFilter Field:=2, Criteria1:=strSubGroup
On Error Resume Next
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Copy Range("A20")
Err.Clear
End With
.AutoFilterMode = False
End With


End Select
Columns("D:D").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("C12").Select
Application.ScreenUpdating = True
End Sub

Does this look ok? It seems to be working anyway

 

[Tom Urtis]

Does this look ok? It seems to be working anyway

Hey, if it works the way you want, then it looks utterly fantastic. The goal is to end up with what works best for you, so if your modifications did the job, then we've collaborated on producing the Mona Lisa of Change events, and now it's on to the next masterpiece.

 

[nofam]

Awesome - thanks again Tom; I've learned so much from doing this.

You rock!