Search Columns & Rows

[Guest]

Hi All

Is there a macro or routine that will read a list of code numbers in col"A"
of Sheet1 then search and find the same code numbers in another 10 sheets in
col"A" then place the information in cols "B" & "C" of Sheet1 in the first
empty col when it finds the code number in any of the other Sheets. Sheet 10
has a complete list of the same codes and I need to exclude it altogether
from the original search

Thanks

Mully

 

[Guest]

Hi Mully,

Name the area of column A with data in each additional worksheet- Look1,
Look2, etc… include an empty cell at the top and bottom of the area.

In column B use a function similar to the following:
=IF((LOOKUP(A1,Look1)=A1),A1,IF((LOOKUP(A1,Look2)=A1),A1,"not found"))
You would need to nest IF functions for each sheet additional sheet. A1 is
the first cell in first worksheet in column A.

LOOKUP will only work with IF function if you populate the cell at the top
of the area with a negative value that will never be reached and the cell at
the bottom of the area with a value that will never be reached. And, you
have to sort each named area, ascending order.

Hope that helps.

PB