|
Updating 1 spreadsheet from another
I have a spreadsheet that contains information on lot number sold, price,
settlement date and who the purchaser is. I have a second spreadsheet that contains lot number, purchaser and settlement date. I would like to be able to set up in the 2nd spreadsheet a formula so that when a new lot number goes into the 1st it updates the second. The second spreadsheet is sorted by Purchaser. I was thinking possibly a comination of VLOOKUP and IF statements but am now completely at a loss as to where to start! |
One way is perhaps something along these lines
Supposing you have in Sheet1 ------------- in cols A to C, data from row2 down Lot# Price Name 1110 3000 DEF 1111 4000 MNO 1112 1000 ABC 1113 2000 JKL 1114 1000 GHI (Names in col C are assumed *unique*) In Sheet2 ------------ You have the names listed down col A in ascending order, data from row2 down Name Lot# SettmtDate ABC DEF GHI JKL MNO etc Put in B2: =IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"",INDEX(Sheet1!A :A,MATCH(A2,Sheet1!C:C,0)) ) Copy down This'll retrieve the lot #'s from Sheet1 for the names listed in col A, viz : Name Lot# SettmtDate ABC 1112 DEF 1110 GHI 1114 JKL 1113 MNO 1111 etc Unmatched names, if any, will return blanks ("") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sal" wrote in message ... I have a spreadsheet that contains information on lot number sold, price, settlement date and who the purchaser is. I have a second spreadsheet that contains lot number, purchaser and settlement date. I would like to be able to set up in the 2nd spreadsheet a formula so that when a new lot number goes into the 1st it updates the second. The second spreadsheet is sorted by Purchaser. I was thinking possibly a comination of VLOOKUP and IF statements but am now completely at a loss as to where to start! |
Hi Max,
Thanks for the response. It works really well. That is fantastic!!! Sally "Max" wrote: One way is perhaps something along these lines Supposing you have in Sheet1 ------------- in cols A to C, data from row2 down Lot# Price Name 1110 3000 DEF 1111 4000 MNO 1112 1000 ABC 1113 2000 JKL 1114 1000 GHI (Names in col C are assumed *unique*) In Sheet2 ------------ You have the names listed down col A in ascending order, data from row2 down Name Lot# SettmtDate ABC DEF GHI JKL MNO etc Put in B2: =IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"",INDEX(Sheet1!A :A,MATCH(A2,Sheet1!C:C,0)) ) Copy down This'll retrieve the lot #'s from Sheet1 for the names listed in col A, viz : Name Lot# SettmtDate ABC 1112 DEF 1110 GHI 1114 JKL 1113 MNO 1111 etc Unmatched names, if any, will return blanks ("") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sal" wrote in message ... I have a spreadsheet that contains information on lot number sold, price, settlement date and who the purchaser is. I have a second spreadsheet that contains lot number, purchaser and settlement date. I would like to be able to set up in the 2nd spreadsheet a formula so that when a new lot number goes into the 1st it updates the second. The second spreadsheet is sorted by Purchaser. I was thinking possibly a comination of VLOOKUP and IF statements but am now completely at a loss as to where to start! |
Sorry me again...
It worked on one cell but then would not copy down correctly. ie it matched the first lot to the name of the builder but it did not do it for the rest of lots I know are listed for that same builder. "Max" wrote: One way is perhaps something along these lines Supposing you have in Sheet1 ------------- in cols A to C, data from row2 down Lot# Price Name 1110 3000 DEF 1111 4000 MNO 1112 1000 ABC 1113 2000 JKL 1114 1000 GHI (Names in col C are assumed *unique*) In Sheet2 ------------ You have the names listed down col A in ascending order, data from row2 down Name Lot# SettmtDate ABC DEF GHI JKL MNO etc Put in B2: =IF(ISNA(MATCH(A2,Sheet1!C:C,0)),"",INDEX(Sheet1!A :A,MATCH(A2,Sheet1!C:C,0)) ) Copy down This'll retrieve the lot #'s from Sheet1 for the names listed in col A, viz : Name Lot# SettmtDate ABC 1112 DEF 1110 GHI 1114 JKL 1113 MNO 1111 etc Unmatched names, if any, will return blanks ("") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sal" wrote in message ... I have a spreadsheet that contains information on lot number sold, price, settlement date and who the purchaser is. I have a second spreadsheet that contains lot number, purchaser and settlement date. I would like to be able to set up in the 2nd spreadsheet a formula so that when a new lot number goes into the 1st it updates the second. The second spreadsheet is sorted by Purchaser. I was thinking possibly a comination of VLOOKUP and IF statements but am now completely at a loss as to where to start! |
ie it matched the first lot to the name of the builder
but it did not do it for the rest of lots I know are listed for that same builder. well <g, it was assumed (stated) in the suggestion that: "Names in col C are assumed *unique*" (in Sheet1) as MATCH will return only the first match found for the name Here's an option to try out since you have repeated names involved in Sheet1 Suppose we have In Sheet1, in cols A to C ------------- Lot# Price Name 1110 3000 DEF 1111 4000 ABC 1112 1000 GHI 1113 2000 DEF 1114 1000 ABC 1115 1000 GHI 1116 5000 DEF 1117 2000 ABC 1118 1000 GHI etc List across in say, E1:G1 the *unique* names: ABC, DEF, GHI Put in E2: =IF($C2="","",IF($C2=E$1,ROW(),"")) Copy across to G2, fill down by as many rows as data is expected in cols A to C, say down to G500? In Sheet2 ------------- List across in A1:C1 the unique names: ABC, DEF, GHI Put in A2: =IF(ISERROR(SMALL(INDIRECT("'Sheet1'!"&CHOOSE(MATC H(A$1,Sheet1!$E$1:$I$1,0), "E:E","F:F","G:G")),ROW(A1))),"",OFFSET(Sheet1!$A$ 1,MATCH(SMALL(INDIRECT("'S heet1'!"&CHOOSE(MATCH(A$1,Sheet1!$E$1:$I$1,0),"E:E ","F:F","G:G")),ROW(A1)),I NDIRECT("'Sheet1'!"&CHOOSE(MATCH(A$1,Sheet1!$E$1:$ I$1,0),"E:E","F:F","G:G")) ,0)-1,)) Copy across to C2, fill down by as many rows as was done in Sheet1, viz. to C500 This'll extract all the lot #s for each name from Sheet1, i.e. for the sample data above you'll get: ABC DEF GHI 1111 1110 1112 1114 1113 1115 1117 1116 1118 Adapt / extend to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Sal" wrote in message ... Sorry me again... It worked on one cell but then would not copy down correctly. ie it matched the first lot to the name of the builder but it did not do it for the rest of lots I know are listed for that same builder. |
| All times are GMT +1. The time now is 07:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com