Vlookup help

mr_mouse

New Member
Joined
Dec 11, 2019
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA 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?

Code:
'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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

mr_mouse

New Member
Joined
Dec 11, 2019
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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"

VBA Code:
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

New Member
Joined
Dec 11, 2019
Messages
8
Office Version
  1. 2010
Platform
  1. Windows
ahh with you :D all is working as desired now :)

Many thanks for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,129,403
Messages
5,636,080
Members
416,896
Latest member
Hozier

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