Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Mass update in Excel based on values in different sheets

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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 576
Default Mass update in Excel based on values in different sheets

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

"Terry Griffiths" wrote in message
m...
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Create New Sheets and Name Them Based on Values in another sheet KennyD Excel Discussion (Misc queries) 2 January 28th 10 07:51 PM
Mass Changing whole sheets individual cell references Ryan Excel Discussion (Misc queries) 2 January 16th 09 04:59 PM
Mass Update! (Excel 97) Phendrena Excel Discussion (Misc queries) 4 February 15th 08 01:18 AM
How do I mass update formulas? karin Harpering Excel Discussion (Misc queries) 5 November 22nd 06 02:41 PM
2 files, mass update comments between files Katycatchesnut Excel Worksheet Functions 0 June 2nd 05 10:16 PM


All times are GMT +1. The time now is 11:27 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"