Populating data from other sheets

  • Thread starter Thread starter kre2000
  • Start date Start date
K

kre2000

Hello all,

I would really appreciate some help with this project I a
developing...

I have 2 sheets in a workbook. Sheet 1 contains an assortment o
information. Column A contains a string.

I would like to highlight a selection of rows on Sheet 1. Then, fo
each row in the selection, I would like the macro to compare th
contents of the cell in Column A to the contents of each cell in Colum
A on Sheet 2. If it finds a match in this column on Sheet 2, the macr
should copy the contents of the cell in Column B to the matched-ro
column B on Sheet 1.

i.e.

Sheet 1
Bob
Mark
John

Sheet 2
Bob bso
John bso
Steve bso
Ray cov
Mark rot
Phil cov

The macro should scan Sheet 1 column a, reference to sheet 2, and ad
data to column b on Sheet 1 to result in:

Sheet 1
Bob bso
Mark rot
John bso

Sheet 2
Bob bso
John bso
Steve bso
Ray cov
Mark rot
Phil cov

Thank you so much! You have no idea how much I appreciate the help
receive at this forum!
 
I could write a macro for you, but will this formula, to be copied for all
cells in column B on Sheet 1, work for you?

=VLOOKUP(A1,Sheet2!A$1:B$4,2,FALSE)
 
I ejoy the simplicity of your formula for sure!
In my example, the formula worked for Bob and John (putting bso i
both.) However it did not capture results for Mark--it put #N/A in th
cell.

I think the problem results from the lookup range (table array) in you
formula not reaching far enough down the b-column. I changed th
formula to the following:

=VLOOKUP(A2,Sheet2!A$1:B$6,2,FALSE)

Is this a correct change or did it just happen to work? (Note:
changed the B$4 to B$6)

Thank you so much
 
You can assign a name to the range of cells. That way when you add
name to the list, it is included. Grant it, you must add the new nam
between the first and last cells in your range.

Michae
 
Ok, how bout this...

If the record is not found (if it prints #N/A), is it possible t
instead display "Record Not Found"
 
=IF(ISNA(MATCH(A2,Sheet2!A1:A6,0)),"Record Not
Found",VLOOKUP(A2,Sheet2!A$1:B$6,2,FALSE))

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Back
Top