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.