Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare and Update elements from Sheet1 with Sheet2
Hello,
Problem: I have 2 spreadsheets. The first spreadsheet (SpreadsheetA) is the master record of our inventory elements. The second spreadsheet (SpreadsheetB) is a copy and paste of some of the elements in SpreadsheetA but have been updated in some way. Is there a script of sorts that will find the serial numbers of B in sheet A and update the appropriate column. For example. SpreadsheetA c1 c2 c3 c4 c5 r1 001 ABC r2 002 BCD r3 003 CDE r4 004 DEF r4 005 EFG SpreadsheetB c1 c2 c3 c4 c5 r1 001 XYZ r2 002 WED r3 003 DF4 r4 004 VR# r4 005 POE I want to take c2 from SpreadsheetB and find the corresponding record in SpreadsheetA and update the elements of c3. Any ideas? Even a partial script would be very helpful! Joel |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare and Update elements from Sheet1 with Sheet2
Look in Help under VLOOKUP and come back if more detailed helps is needed
best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email wrote in message ... Hello, Problem: I have 2 spreadsheets. The first spreadsheet (SpreadsheetA) is the master record of our inventory elements. The second spreadsheet (SpreadsheetB) is a copy and paste of some of the elements in SpreadsheetA but have been updated in some way. Is there a script of sorts that will find the serial numbers of B in sheet A and update the appropriate column. For example. SpreadsheetA c1 c2 c3 c4 c5 r1 001 ABC r2 002 BCD r3 003 CDE r4 004 DEF r4 005 EFG SpreadsheetB c1 c2 c3 c4 c5 r1 001 XYZ r2 002 WED r3 003 DF4 r4 004 VR# r4 005 POE I want to take c2 from SpreadsheetB and find the corresponding record in SpreadsheetA and update the elements of c3. Any ideas? Even a partial script would be very helpful! Joel |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare and Update elements from Sheet1 with Sheet2
I was being lazy:
=VLOOKUP(A1,Sheet1!A1:B100,2,FALSE) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email wrote in message ... Hello, Problem: I have 2 spreadsheets. The first spreadsheet (SpreadsheetA) is the master record of our inventory elements. The second spreadsheet (SpreadsheetB) is a copy and paste of some of the elements in SpreadsheetA but have been updated in some way. Is there a script of sorts that will find the serial numbers of B in sheet A and update the appropriate column. For example. SpreadsheetA c1 c2 c3 c4 c5 r1 001 ABC r2 002 BCD r3 003 CDE r4 004 DEF r4 005 EFG SpreadsheetB c1 c2 c3 c4 c5 r1 001 XYZ r2 002 WED r3 003 DF4 r4 004 VR# r4 005 POE I want to take c2 from SpreadsheetB and find the corresponding record in SpreadsheetA and update the elements of c3. Any ideas? Even a partial script would be very helpful! Joel |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare and Update elements from Sheet1 with Sheet2
Hi Bernard,
Thanks for the reply! I ended up using Index and Match instead of Vlookup since I needed to return the value of another cell. At the end of it all my formula looks like this: =IF(ISNA(MATCH( <item_searching_for , <column_looked_in , 0)),"",INDEX( <sheet_looked_in ,MATCH(<item_searching_for , <column_looked_in ,0),8)) In English: Find for item in a column of Sheet1 in a column of Sheet2 (Match function returns a row #). If it returns an error, then it's not found and return a blank. If it returns a row #, then give me cell contents at the intersection of that row # and a constant column. =IF(ISNA(MATCH('IS Inv'!J2,Temp!K:K,0)),"",INDEX(Temp! $1:$65536,MATCH('IS Inv'!J2,Temp!K:K,0),8)) This appears very complicated and I'm sure it can be broken down into an easier and shorter formula, but it works for me! I hope this helps anyone else who is doing a similar process. Thanks again Bernard, for pointing me in the right direction. Joel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') | Excel Worksheet Functions | |||
How to reference C5, D5, E5 from sheet2 in A1, A2, A3 of sheet1? | Excel Worksheet Functions | |||
merging sheet1 to sheet2 | Excel Worksheet Functions | |||
Moving from 1 row sheet1 to sheet2 | Excel Worksheet Functions |