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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

DataBlake

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

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

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

DataBlake

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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

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

Code:
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
 
Last edited:

mr_mouse

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

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

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

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

mr_mouse

New Member
Joined
Dec 11, 2019
Messages
8
Office Version
  1. 2010
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.
spoke to soon it was the line ws.Range("B12:P26").ClearContents
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,010
Members
416,892
Latest member
Bensch

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