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]

perhaps you can post the data, screenshots, or even upload the file?
i follow what you're saying until you get to the formula
seeing is believing in this case so any examples you can provide, and an expected result will help

 

[mr_mouse]

perhaps you can post the data, screenshots, or even upload the file?
i follow what you're saying until you get to the formula
seeing is believing in this case so any examples you can provide, and an expected result will help

Hi, Sure

my file can be found

DashboardESP.xlsx

drive.google.com

Im not 100% sure what ive done so far is the best approach

 

[DataBlake]

some of the functionality of the sheet seems to be broken from your proofing but i think i get the idea.
in the dashboard you want client dropdown (which seems to be broken for me) and "air conditioning" dropdown (which does work for me) to return the relevant info in the facility category box?
just making sure before i do anything, but if i select Name 1 and Air Conditioning 1 you want "314 ABM Technical Solutions" to be returned?

this is an okay way of doing it, but its usually advised against because of reference errors and the like.
I know powerquery and pivot tables would be probably the most optimal for what you're looking to do

but if you're just looking for a match solution it will be a kind of complex 2 dimensional array match/index
a VBA code would probably be easier/less problematic if you're okay with that?

 

[DataBlake]

I have a sample VBA code that will ultimately do what you're looking to do i just have to alter it (tomorrow) to produce the results for all of the air conditioners rather than just the first one.
i'll check back tomorrow for your response

 

[mr_mouse]

Hi
thanks for the advice, ive not touched vba in a long while but I'd take any help to solve this, excel isnt really my strong suit.

I was hoping if it was possible, that you'd select the name and then air conditoning and it would show all 5 air conditioning values vertically. some of the data on the sheet such as "256" (L2) relate to a cell in another sheet that im probably going to do another vlook up on, that will also be displayed in that middle box.

im not sure why the client drop down isnt working for you, just retested on my pc, if i blank the celll then select the drop down I get 3 choices that populate the different address cells.

Thanks again for your help

 

[DataBlake]

okay try this out:

Go to your vba screen (alt + F11)
under "Microsoft Excel Objects" click on Sheet1(dashboard) and copy paste this code in the box

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("D11")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        Call dothething

End If
End Sub

and then right click any of the Microsoft Excel Objects and select Insert > Module
copy and paste this code into the box

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

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

If IsEmpty(ws.Range("B12")) = False Then
    ws.Range("B12:P26").ClearContents
End If

If IsNumeric(ary2(1)) = False And ary2(1) = "" Then
    MsgBox "Please select a name to generate a facility category"
    GoTo err
End If

j = 0
For x = 1 To UBound(ary1, 2)
    If InStr(1, ary1(1, x), ary2(2), vbTextCompare) Then
        For y = 2 To UBound(ary1)
            If ary1(y, 2) = ary2(1) Then
                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
                j = j + 1
            End If
        Next y
    End If
Next x
 


err:
End Sub

you can tell me what you need from there.
but the way it works is every time you change D11 on the dashboard it will update the facility category

 

[mr_mouse]

okay try this out:

Go to your vba screen (alt + F11)
under "Microsoft Excel Objects" click on Sheet1(dashboard) and copy paste this code in the box

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range("D11")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        Call dothething

End If
End Sub

and then right click any of the Microsoft Excel Objects and select Insert > Module
copy and paste this code into the box

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

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

If IsEmpty(ws.Range("B12")) = False Then
    ws.Range("B12:P26").ClearContents
End If

If IsNumeric(ary2(1)) = False And ary2(1) = "" Then
    MsgBox "Please select a name to generate a facility category"
    GoTo err
End If

j = 0
For x = 1 To UBound(ary1, 2)
    If InStr(1, ary1(1, x), ary2(2), vbTextCompare) Then
        For y = 2 To UBound(ary1)
            If ary1(y, 2) = ary2(1) Then
                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
                j = j + 1
            End If
        Next y
    End If
Next x



err:
End Sub

you can tell me what you need from there.
but the way it works is every time you change D11 on the dashboard it will update the facility category

Hi :D

Thats some wizardry right there! I dont really understand the code 100% yet but it works a charm! brings back all the ranges perfectly.


Thanks so much fooor your help!

 

[mr_mouse]

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.

 

[mr_mouse]

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.

spoke to soon it was the line ws.Range("B12:P26").ClearContents