Hello,
I am looking to input a number and have a formula search multiple sheets for that number in column A, and return all values in column B associated with the input. I have my sheets named Book 1, Book 2, etc. Under the DWG # column you will see that some numbers are repeated in the same sheet and on other sheets. For example, DWG # 8306305 appears on Book 1 (twice), on Book 2 (once) and Book 3 (once). I would like to have all the corresponding ECN #'s be outputted. For example, input 8306305 and in the next column I want to see 2, 145, 621, 700. It doesn't matter if the ECN #'s are in the same cell or positioned vertically. As you will see I have figured out how to return the very first ECN # of the inputted value, however, I need all of them. I have hit a dead end with google. Thank you!
P.S. I should also add that in the end each "book" will have ~200 or so values. Just trying to prove a concept right now.
How about a little macro to do it?
Sub LookupDWG()
Dim ws As Worksheet
Dim DWG As String
Dim i As Integer
Dim LR As Long
Dim c As Range
Dim DWGr As Range
DWG = Worksheets("MAIN SEARCH").Range("A2")
i = 1
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "NAME RANGE" Or ws.Name = "MAIN SEARCH" Then
GoTo SkipI
End If
LR = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set DWGr = ws.Range("a1:a" & LR)
For Each c In DWGr
If c.Value = DWG Then
i = i + 1
Worksheets("MAIN SEARCH").Range("B" & i) = c.Offset(0, 1)
End If
Next c
SkipI:
Next ws
End Sub
That worked quite well, thank you! One last thing here. I noticed that when I search for a second DWG # that it won't clear out the results from the previous search. Say I return 5 results on my first search and then on my next search only 2 results come back. Well, a total of 5 results will be there but only the first (2) will related to the second search and the last 3 are related to the first search. How could I remedy this? Thank you for your help!
With a small adaptation of the Purfleet code 😉 ...
LastRow = Range("B1").End(xlDown).Row
Sheets("MAIN SEARCH").Range("B2:B" & LastRow).ClearContents
