Vlookup help

[mr_mouse]

Hi guys,

im doing a favor for a friend, and trying to add a dashboard to a spreadsheet for him

I've said to him multiple times this should really be a database and would make my life easier but it 100% has to be a spreadsheet

He has a work sheet set out like:

Ref | Customer name | address | more address | telephone number| Air con 1 | air con 2 | plastering 1 |plastering 2| Plastering 3| etc etc

So far, ive made a searchable drop down that brings back the Ref and address stuff. He can search the customer and get that info quickly instead of scrolling around a work sheet.

Ive made a second sheet to hold some formulas too make a dynamic search box
=IF(ISNUMBER(SEARCH(Dashboard!$D$5,'Site List'!B2)),MAX($D$1:D1)+1,0)
=OFFSET($H$2,,,COUNTIF($H$2:$H$93,"?*"))

used a few vlookup forumlas on the dashboard to pull back the address info like; =IF(LEN(VLOOKUP($D$5,'Site List'!$B$2:$H$93,2,FALSE))=0,"",VLOOKUP($D$5,'Site List'!$B$2:$H$93,2,FALSE))

Ive made a drop down based on all the other headings in the spreadsheet. making it so there is just one of each type ie Air con, Plastering, CCTV

what im trying to figure out is, how match the rows based on the values set in the first and second drop down and display them on the dashboard Does that make sense?

Many thanks for any help.

 

[DataBlake]

Back again,
just one minor thing, so far I have that big square in the middle, Ive tried to remove some of the cells s its not as tall (16 rows high is a bit to much) when I do it breaks, but I cant see where this is referenced in the code.

               ws.Cells(12, 2 + j).Value2 = ary1(1, x)
                ws.Cells(13, 2 + j).Value2 = ary1(y, x)
                ws.Cells(12, 2 + j).EntireColumn.AutoFit

this bit is where the box is referenced
12 rows down, 2 columns + how many matches
I'll just go ahead and note everything, but what cells were deleted/changed?

'establishes two arrays, one for site list, one for the match criteria in dashboard
Sub dothething()
Dim x As Long, y As Long
Dim ary1 As Variant, ary2(1 To 2) As String
Dim os As Worksheet, ws As Worksheet

'here i establish shortcut names for worksheets and load establish the data for the arrays we created
Set ws = Sheets("Dashboard")
Set os = Sheets("Site List")
ary1 = os.Range("A1").CurrentRegion.Value2
ary2(1) = ws.Range("B3").Value2
ary2(2) = ws.Range("D11").Value2

'this just says that if there is data between B12:P26 on the dashboard to delete it
If IsEmpty(ws.Range("B12")) = False Then
    ws.Range("B12:P26").ClearContents
End If

'this says that if the name in the array is blank and contains no numerical data to exit the sub and display a message
If IsNumeric(ary2(1)) = False And ary2(1) = "" Then
    MsgBox "Please select a name to generate a facility category"
    GoTo err
End If

'we use j to count how many matches we find
j = 0

'here we start looping through the headers on site list
For x = 1 To UBound(ary1, 2)

    'if the header contains the keyword from dashboard then...
    If InStr(1, ary1(1, x), ary2(2), vbTextCompare) Then
        
        'we start a loop through the rows of site list
        For y = 2 To UBound(ary1)
        
            'if the name matches the name in the array...
            If ary1(y, 2) = ary2(1) Then
            
                'we take the 12th row and the 2nd column + j and set it to the value of the header in the array
                ws.Cells(12, 2 + j).Value2 = ary1(1, x)
                'we take the 13th row and the 2nd column + j and set it to the value of the match in the array
                ws.Cells(13, 2 + j).Value2 = ary1(y, x)
                'we make the column as wide as the text
                ws.Cells(12, 2 + j).EntireColumn.AutoFit
                
                'we increment j every time a keyword is found in the header
                j = j + 1
            End If
        Next y
    End If
Next x
 

'this is the part that exits the sub if no name is found
err:
End Sub

 

[mr_mouse]

ahh thanks for the code comments very handy

the codes those numbers bring back relate to another sheet so I put some data below the search which was being wiped out due the selection so I shortened "ws.Range("B12:P26").ClearContents" to ws.Range("B12:P14").ClearContents. Which seemed to fix it. Do I need to make the same change to "ws.Cells(12, 2 + j)" ?

Cheers

 

[DataBlake]

Do I need to make the same change to "ws.Cells(12, 2 + j)" ?

that's what i'm asking you ?
the target for the data inside the box is still B12 correct? if thats the case you shouldnt have to change it
but if you move the box to the right (column C start) you would change "2" to "3"
if you moved the box up or down you would change "12"

ary2(1) = ws.Range("B3").Value2
ary2(2) = ws.Range("D11").Value2

relates to cells as well so if you moved the name or the appliance dropdown you would have to change these to the proper cells
the reason i use range for one and cells for another is because i don't have to do math with static cells.

 

[mr_mouse]

ahh with you :D all is working as desired now

Many thanks for your help