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

 

[lenze]

Have a look at Advanced Filter or MSQuery!!

lenze

 

[nofam]

Hi Lenze,

I've tried the advanced filter, but I can't seem to get it to do what I want; at any rate, I don't want the whole row of filtered data, just the cells containing SKU, description, and price.

Any tips?

 

[ssalazarjr]

i just typed in how to do it but mr. excel timed me out and erased what i typed in

 

[Tom Urtis]

It helps if you provide some points of reference, examples:

What is the sheet name holding the list of 12000 records.
What is the range (columns and rows) where these records exist on that sheet.
What is the sheet name where you want the copied records to go.
What is the range (starting in what row and column) where you want the copied records to go.
What is the sheet name holding the two data validation cells.
What are the cell addresses of the data validation cells.

Details details details...it helps to tell people what you are looking at.

 

[nofam]

Hi Tom,

Thanks for your time; sorry I didn't provide this off the bat!

It helps if you provide some points of reference, examples:

What is the sheet name holding the list of 12000 records. Sheet2

What is the range (columns and rows) where these records exist on that sheet. Range name of match

What is the sheet name where you want the copied records to go. Sheet4

What is the range (starting in what row and column) where you want the copied records to go. B20

What is the sheet name holding the two data validation cells. Sheet4

What are the cell addresses of the data validation cells. Department is in C12, Sub-group in C14



Details details details...it helps to tell people what you are looking at.

The sheet names are pretty arbitrary at this stage of course.

Thanks again!

Cheers
Chris

ray:

 

[Tom Urtis]

Just for starters, right click on your Sheet4 tab, left click on View Code, and paste this into the large white pane that is the Sheet4 worksheet module.

Press Alt+Q to return to the worksheet.

Test the code by selecting items in the drop downs of cells C12 and C14 on Sheet4.

Assumes your workbook is set up as you say, with the range name "match" (note, that's "match" as you posted it, not "Match") on Sheet2.

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$, myConf%
strDepartment = Range("C12").Value
strSubGroup = Range("C14").Value
myConf = MsgBox("Do you want to import the records from your match range" & vbCrLf & _
"to this sheet for " & strDepartment & " and " & strSubGroup & "?", 36, "Show these records???")
Select Case myConf
Case 7
MsgBox "No problem, nothing different will be shown.", , "You clicked No"
Exit Sub
Case 6
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
Application.ScreenUpdating = True
End Select
End Sub

 

[nofam]

Hi Tom,

That's fantastic - thank you so much!

 

[nofam]

Hi Tom,

Just wondering if there's any way your code can be tweaked to allow for the word (ALL) to be used in the Sub-group list, so that it's only filtering by Department; i.e. if the user wanted to look at all products in BUILDING SUPPLIES without restricting it further to any of the Sub-groups in that Department?

Thanks again!

 

[Tom Urtis]

To do that, delete the entire code (all of it) that I first posted, and copy this into that Sheet4 worksheet module instead.

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$, myConf%
strDepartment = Range("C12").Value
strSubGroup = Range("C14").Value
myConf = MsgBox("Do you want to import the records from your match range to this sheet" & vbCrLf & _
"for " & strDepartment & " AND for " & strSubGroup & "?" & vbCrLf & vbCrLf & _
"Click ''Yes'' to show records for " & strDepartment & " *AND* for " & strSubGroup & "." & vbCrLf & _
"Click ''No'' to show records for *ALL* " & strDepartment & " subgroups." & vbCrLf & _
"Click ''Cancel'' to not copy any records at all to this sheet.", 35, "Which records to show ?")
 
With Sheets("Sheet2")
Select Case myConf
 
Case 2
Exit Sub
 
Case 6
Application.ScreenUpdating = False
Rows("20:" & Rows.Count).Clear
.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
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
 
Case 7
Application.ScreenUpdating = False
Rows("20:" & Rows.Count).Clear
.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
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
 
End Select
End With
End Sub