software

I want to use Excel to work between two worksheets to pull out a value from
one and stick it in the other. I tried using DGET but am I having trouble
because of the Criteria syntax. I want to use the first three columns as
criteria to fill in the third. Below is a very basic example - I'm talking
about doing this with thousands of lines of Excel data! I'm not sure if I
should be using DGET or something else, but it needs to pull the quot;weightquot;
from the second worksheet and put it in the appropriate row in the first
worksheet . . . PLEASE HELP!

Worksheet 1
Name Age Sex Weight
Alex 10 M
Corey 10 M
Elvis 9 MWorksheet 2
Name Age Sex Weight
Alex 10 M 100
Alex 12 M 130
Brian 11 M 123
Corey 10 M 98
Dan 10 M 109
Elvis 9 M 96Hi!

If there are possible duplicates this might be a problem.

Assume Sheet1:

Names in column A, A2:A100
Age in column B, B2:B100
Sex in column C, C2:C100

Same setup in Sheet2 and weight is in column D,D2100

Enter this formula in sheet1 D2:

=SUMPRODUCT(--(Sheet2!A$2:A$100=A$2),--(Sheet2!B$2:B$100=B$2),--(Sheet2!C$2:C$100=C$2),Sheet2!D$2$100)

Copy down as needed.

Biff

quot;Werf Nortquot; lt;Werf gt; wrote in message
...
gt;I want to use Excel to work between two worksheets to pull out a value from
gt; one and stick it in the other. I tried using DGET but am I having trouble
gt; because of the Criteria syntax. I want to use the first three columns as
gt; criteria to fill in the third. Below is a very basic example - I'm
gt; talking
gt; about doing this with thousands of lines of Excel data! I'm not sure if I
gt; should be using DGET or something else, but it needs to pull the quot;weightquot;
gt; from the second worksheet and put it in the appropriate row in the first
gt; worksheet . . . PLEASE HELP!
gt;
gt; Worksheet 1
gt; Name Age Sex Weight
gt; Alex 10 M
gt; Corey 10 M
gt; Elvis 9 M
gt;
gt;
gt; Worksheet 2
gt; Name Age Sex Weight
gt; Alex 10 M 100
gt; Alex 12 M 130
gt; Brian 11 M 123
gt; Corey 10 M 98
gt; Dan 10 M 109
gt; Elvis 9 M 96
gt;
Ooops!

Better make the criteria row references relative:

=SUMPRODUCT(--(Sheet2!A$2:A$100=A2),--(Sheet2!B$2:B$100=B2),--(Sheet2!C$2:C$100=C2),Sheet2!D$2$100)Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; If there are possible duplicates this might be a problem.
gt;
gt; Assume Sheet1:
gt;
gt; Names in column A, A2:A100
gt; Age in column B, B2:B100
gt; Sex in column C, C2:C100
gt;
gt; Same setup in Sheet2 and weight is in column D,D2100
gt;
gt; Enter this formula in sheet1 D2:
gt;
gt; =SUMPRODUCT(--(Sheet2!A$2:A$100=A$2),--(Sheet2!B$2:B$100=B$2),--(Sheet2!C$2:C$100=C$2),Sheet2!D$2$100)
gt;
gt; Copy down as needed.
gt;
gt; Biff
gt;
gt; quot;Werf Nortquot; lt;Werf gt; wrote in message
gt; ...
gt;gt;I want to use Excel to work between two worksheets to pull out a value
gt;gt;from
gt;gt; one and stick it in the other. I tried using DGET but am I having
gt;gt; trouble
gt;gt; because of the Criteria syntax. I want to use the first three columns
gt;gt; as
gt;gt; criteria to fill in the third. Below is a very basic example - I'm
gt;gt; talking
gt;gt; about doing this with thousands of lines of Excel data! I'm not sure if
gt;gt; I
gt;gt; should be using DGET or something else, but it needs to pull the quot;weightquot;
gt;gt; from the second worksheet and put it in the appropriate row in the first
gt;gt; worksheet . . . PLEASE HELP!
gt;gt;
gt;gt; Worksheet 1
gt;gt; Name Age Sex Weight
gt;gt; Alex 10 M
gt;gt; Corey 10 M
gt;gt; Elvis 9 M
gt;gt;
gt;gt;
gt;gt; Worksheet 2
gt;gt; Name Age Sex Weight
gt;gt; Alex 10 M 100
gt;gt; Alex 12 M 130
gt;gt; Brian 11 M 123
gt;gt; Corey 10 M 98
gt;gt; Dan 10 M 109
gt;gt; Elvis 9 M 96
gt;gt;
gt;
gt;
Don't think DGET is the best way to go,=INDEX(Sheet2!$D$4:$D$1000,MATCH(1,(Sheet2!$A$4:$A $1000=A3)*(Sheet2!$B$4:$B$1000=B3)*(Sheet2!$C$4:$C $1000=C3),0))where Sheet2A41000 is the database with weight in D41000, names in
A4:A1000 and so on
Alex in your example is in A3

entered with ctrl shift amp; enter then copied down will return

100
98
96--
Regards,

Peo Sjoblom

Portland, Oregon

quot;Werf Nortquot; lt;Werf gt; wrote in message
...
gt;I want to use Excel to work between two worksheets to pull out a value from
gt; one and stick it in the other. I tried using DGET but am I having trouble
gt; because of the Criteria syntax. I want to use the first three columns as
gt; criteria to fill in the third. Below is a very basic example - I'm
gt; talking
gt; about doing this with thousands of lines of Excel data! I'm not sure if I
gt; should be using DGET or something else, but it needs to pull the quot;weightquot;
gt; from the second worksheet and put it in the appropriate row in the first
gt; worksheet . . . PLEASE HELP!
gt;
gt; Worksheet 1
gt; Name Age Sex Weight
gt; Alex 10 M
gt; Corey 10 M
gt; Elvis 9 M
gt;
gt;
gt; Worksheet 2
gt; Name Age Sex Weight
gt; Alex 10 M 100
gt; Alex 12 M 130
gt; Brian 11 M 123
gt; Corey 10 M 98
gt; Dan 10 M 109
gt; Elvis 9 M 96
gt;Thanks both of you!