Lookup Formula help




There is probably a simple solution to this, but I just can't seem to get my head around it.


I have a drop-down box and I need to be able to identify all of the tabs that the data in the drop down appears.  There are 135 tabs, they are all laid out exatly the same.


I have worked out how to count how many times it appears but I just need to know all of the tabs that it appears in, example attached


Thanks in advance

5 Replies

@Machala Sentance 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsh As Worksheet
    Dim rng As Range
    Dim r As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("C3:D" & Rows.Count).ClearContents
    If Not Intersect(Range("B3"), Target) Is Nothing Then
        If Range("B3").Value <> "" Then
            r = 2
            For Each wsh In ThisWorkbook.Worksheets
                If wsh.Name <> "Summary" Then
                    Set rng = wsh.Range("B:B").Find(What:=Range("B3").Value, LookAt:=xlWhole)
                    If Not rng Is Nothing Then
                        r = r + 1
                        Range("C" & r).Value = wsh.Name
                        Range("D" & r).Value = rng.Offset(0, 1).Value
                    End If
                End If
            Next wsh
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.

See the attached version.

Hi Hans,

Thanks for the quick reply - rooky mistake on the example, if my look up is in A158:A175, and the drop down is in B5, which range bit is which? (I should have put them in different cells - sorry not good with VBA - I have changed the sheet name it's just the look up bits/ranges.

Thanks again

@Machala Sentance 

What should be the output range?

best response confirmed by Machala Sentance (Contributor)
The data/activity will be in A158:A175 on each sheet and the dropdown will be on tab called "Staff by Activity" in cell B5, and all the names listed in column C - like the example, I think it's just where it needs to look that I am unsure about in the coding,

Thanks again
@Hans Vogelaar

Hi Hans, thanks for your help I have managed to work it out,

Thanks again