Looking to find cell value among specific sheets in workbook

[Nomas]

Hi,

Thanks for looking at my question. I am working in Excel 2016.

I want to look up a specific cell value from among a range of worksheets (in the same workbook) and have it return the worksheet name the value is found on.

The sample page from the workbook is in the link below, i tried to insert the picture but was not able to get it to work.

https://imgur.com/Ga7oYlN


The values to be looked up are on sheet: Main (pictured), in cell D2 (Column Name "Item"). I want to return the looked up values to the same sheet but in cell F2 (Column Name "Site"). Then I would fill down to populate the remaining cells in column F.

The sheets in the workbook to be queried are Front, Back, Off-Set, and Turn-Over. The values on the list only occur once across all 4 worksheets. Please let me know if you need any clarification. I really appreciate any help.

Thanks,

John

 

[steve the fish]

Try something like this:

arr = Array("Front", "Back", "Off-Set", "Turn-Over")

With Sheets("Main")
    lr = .Range("D" & .Rows.Count).End(xlUp).Row
    If lr > 1 Then
        For Each c In .Range("D2:D" & lr)
            For i = LBound(arr) To UBound(arr)
                Set myVal = Sheets(arr(i)).Cells.Find(c.Value, , , xlWhole)
                If Not myVal Is Nothing Then
                    c.Offset(0, 2) = myVal.Parent.Name
                    Exit For
                End If
            Next
        Next
    End If
End With

 

[Nomas]

Hi Steve,

I appreciate the response and code. I used to be more savvy with excel but haven't really used it much in years. How do I get this to work? I assume I pop this in a module but not sure how to call/execute it. Sorry for being dense, appreciate any guidance.

Regards,

John

 

[steve the fish]

Go to the workbook in question. Press ALT and F11. In the white space paste this:

Sub nomas()

Dim arr, lr As Long, c As Range, i As Long

arr = Array("Front", "Back", "Off-Set", "Turn-Over")

With Sheets("Main")
    lr = .Range("D" & .Rows.Count).End(xlUp).Row
    If lr > 1 Then
        For Each c In .Range("D2:D" & lr)
            For i = LBound(arr) To UBound(arr)
                Set myVal = Sheets(arr(i)).Cells.Find(c.Value, , , xlWhole)
                If Not myVal Is Nothing Then
                    c.Offset(0, 2) = myVal.Parent.Name
                    Exit For
                End If
            Next
        Next
    End If
End With

End Sub

Close the VBA window. Press view then macros. Double click the 'nomas' macro. You will have to save the workbook as a macro enabled (.xlsm) file.

 

[Nomas]

Steve the fish, you are awesome!

This works like a charm! Thank you so very much. Its greatly appreciated!