Mass update in Excel based on values in different sheets

[Terry Griffiths]

Hi,

I have a problem. I have 2 sheets in a spreadsheet, "sheet1" and
"sheet2", in sheet 1 i have 2 columns A and B, in Sheet2 i have also 2
columns A and B.
Sheet1.column A is a customer number. Sheet2.column A is also a
customer number. Sheet1column b is empty. I need to fill this empty
column with sheet2.column B, when each cell in sheet 1 and 2 column A
are equal. What is the best way to do this with VB or directly in
Excel.

 

[steve]

Terry,

Don't know if there is a "best way". With Excel there are as many ways as
imagination allows.

Much depends on whether you want to work with macros or formulas.

A simple formula in column B on Sheet1 can easily do the trick (put in B1):
=If(Sheet1!A1=Sheet2!A1,Sheet2!B1,"")
than just fill it down to the last row (determined by column A)

Note: if row 1 is a header row, change A1 & B1 to A2 & B2.

You can also incorporate this into a macro.

Now this is to match values row by row between sheets. But if Sheet2 has
more entries than Sheet1 and the
2 sheets are not a row by row match - but you still want to retrieve the
data from Sheet2:
=Index(Sheet2!B:B,Match(A1,Sheet2!A:A,0),1)
this will find the value of Sheet1!A1 in Sheet2 column A and return the
corresponding value in Sheet2 column B.

steve