SOLVED

Lookup Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-2367522%22%20slang%3D%22en-US%22%3ELookup%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2367522%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20probably%20a%20simple%20solution%20to%20this%2C%20but%20I%20just%20can't%20seem%20to%20get%20my%20head%20around%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20drop-down%20box%20and%20I%20need%20to%20be%20able%20to%20identify%20all%20of%20the%20tabs%20that%20the%20data%20in%20the%20drop%20down%20appears.%26nbsp%3B%20There%20are%20135%20tabs%2C%20they%20are%20all%20laid%20out%20exatly%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20worked%20out%20how%20to%20count%20how%20many%20times%20it%20appears%20but%20I%20just%20need%20to%20know%20all%20of%20the%20tabs%20that%20it%20appears%20in%2C%20example%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2367522%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2367628%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2367628%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F158100%22%20target%3D%22_blank%22%3E%40Machala%20Sentance%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20wsh%20As%20Worksheet%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20Range(%22C3%3AD%22%20%26amp%3B%20Rows.Count).ClearContents%0A%20%20%20%20If%20Not%20Intersect(Range(%22B3%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20Range(%22B3%22).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%202%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20Each%20wsh%20In%20ThisWorkbook.Worksheets%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20wsh.Name%20%26lt%3B%26gt%3B%20%22Summary%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20wsh.Range(%22B%3AB%22).Find(What%3A%3DRange(%22B3%22).Value%2C%20LookAt%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20If%20Not%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20r%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22C%22%20%26amp%3B%20r).Value%20%3D%20wsh.Name%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Range(%22D%22%20%26amp%3B%20r).Value%20%3D%20rng.Offset(0%2C%201).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20wsh%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2367652%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2367652%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20the%20quick%20reply%20-%20rooky%20mistake%20on%20the%20example%2C%20if%20my%20look%20up%20is%20in%20A158%3AA175%2C%20and%20the%20drop%20down%20is%20in%20B5%2C%20which%20range%20bit%20is%20which%3F%20(I%20should%20have%20put%20them%20in%20different%20cells%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20-%20sorry%20not%20good%20with%20VBA%20-%20I%20have%20changed%20the%20sheet%20name%20it's%20just%20the%20look%20up%20bits%2Franges.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20again%3C%2FLINGO-BODY%3E
Contributor

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

@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)
Solution
@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
@Hans Vogelaar

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

Thanks again

Machala