 SOLVED

# Lookup Formula help

Hi,

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

# Re: Lookup Formula help

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.

# Re: Lookup Formula help

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

# Re: Lookup Formula help

What should be the output range?

best response confirmed by Machala Sentance (Contributor)
Solution

# Re: Lookup Formula help

@hansvogelaar
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

# Re: Lookup Formula help

@Hans Vogelaar

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

Thanks again

Machala