Retuyrning a single entry when multiples of the same
 

Can somebody figure out this one? I have two worksheets; Worksheet 1 as all
the data and Worksheet 2 is a report that has to be sent to the board.

On Worksheet 2, how to I get a SINGLE entry when I make reference to a Board
# that can repeat itself on Worksheet 1.

Worksheet 1

A B E F G
1 DATE Inv. # Cwt Sales PMB #
2 Amount
3
4 01-Jul-09 27173 300.00 1000.00
5 01-Jul-09 27174 200.00 1000.00 11703
6 01-Jul-09 27175 0.50 1000.00
7 02-Jul-09 27176 1.00 1000.00
8 02-Jul-09 27177 1.50 1000.00
9 02-Jul-09 27178 96 1000.00 11714
10 02-Jul-09 27178 126.00 1000.00 11714
11 02-Jul-09 27178 144.00 1000.00 11714
12 02-Jul-09 27180 25 1000.00

Worksheet 2
A B C D
1 Date Board# CWT Invoice #
2
3 01-Jul-09 11703 200 27174
4 02-Jul-09 11714 366 27178

The formula in my mind would go something like this IN WORKSHEET 2.

In cell A3 to get the date; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 A:A
In cell d3 to get the invoice; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 B:B

The problem occurs when there is more then one instance of the same Board #;
The dates and invoices will always be the same for one board #.

Hope everybody enjoys this problem!

Retuyrning a single entry when multiples of the same
 

Hi Barbara

In Sheet2 you just need to typein the board number in ColB...The other cells
will hold the below formula which will lookin Sheet1 ..Try the below

In A3
=INDEX(Sheet1!A:A,MATCH(B3,Sheet1!G:G,0))

In C3
=SUMIF(Sheet1!G:G,B3,Sheet1!E:E)

In D3
=INDEX(Sheet1!B:B,MATCH(B3,Sheet1!G:G,0))

If this post helps click Yes
---------------
Jacob Skaria


"Barbara" wrote:

Can somebody figure out this one? I have two worksheets; Worksheet 1 as all
the data and Worksheet 2 is a report that has to be sent to the board.

On Worksheet 2, how to I get a SINGLE entry when I make reference to a Board
# that can repeat itself on Worksheet 1.

Worksheet 1

A B E F G
1 DATE Inv. # Cwt Sales PMB #
2 Amount
3
4 01-Jul-09 27173 300.00 1000.00
5 01-Jul-09 27174 200.00 1000.00 11703
6 01-Jul-09 27175 0.50 1000.00
7 02-Jul-09 27176 1.00 1000.00
8 02-Jul-09 27177 1.50 1000.00
9 02-Jul-09 27178 96 1000.00 11714
10 02-Jul-09 27178 126.00 1000.00 11714
11 02-Jul-09 27178 144.00 1000.00 11714
12 02-Jul-09 27180 25 1000.00

Worksheet 2
A B C D
1 Date Board# CWT Invoice #
2
3 01-Jul-09 11703 200 27174
4 02-Jul-09 11714 366 27178

The formula in my mind would go something like this IN WORKSHEET 2.

In cell A3 to get the date; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 A:A
In cell d3 to get the invoice; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 B:B

The problem occurs when there is more then one instance of the same Board #;
The dates and invoices will always be the same for one board #.

Hope everybody enjoys this problem!

Retuyrning a single entry when multiples of the same
 

Hi Barbara,
in column A enter

=vlookup(B3,worksheet1!A:G,1,false)

in C enter

=sumproduct(--(B3=worksheet1!G:G),worksheet1!E:E)

in D enter

=vlookup(B3,worksheet1!A:G,2,false)

"Barbara" wrote:

Can somebody figure out this one? I have two worksheets; Worksheet 1 as all
the data and Worksheet 2 is a report that has to be sent to the board.

On Worksheet 2, how to I get a SINGLE entry when I make reference to a Board
# that can repeat itself on Worksheet 1.

Worksheet 1

A B E F G
1 DATE Inv. # Cwt Sales PMB #
2 Amount
3
4 01-Jul-09 27173 300.00 1000.00
5 01-Jul-09 27174 200.00 1000.00 11703
6 01-Jul-09 27175 0.50 1000.00
7 02-Jul-09 27176 1.00 1000.00
8 02-Jul-09 27177 1.50 1000.00
9 02-Jul-09 27178 96 1000.00 11714
10 02-Jul-09 27178 126.00 1000.00 11714
11 02-Jul-09 27178 144.00 1000.00 11714
12 02-Jul-09 27180 25 1000.00

Worksheet 2
A B C D
1 Date Board# CWT Invoice #
2
3 01-Jul-09 11703 200 27174
4 02-Jul-09 11714 366 27178

The formula in my mind would go something like this IN WORKSHEET 2.

In cell A3 to get the date; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 A:A
In cell d3 to get the invoice; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 B:B

The problem occurs when there is more then one instance of the same Board #;
The dates and invoices will always be the same for one board #.

Hope everybody enjoys this problem!

Retuyrning a single entry when multiples of the same
 

Thank you so much Jacob. Just could not quite get it to work before.

"Jacob Skaria" wrote:

Hi Barbara

In Sheet2 you just need to typein the board number in ColB...The other cells
will hold the below formula which will lookin Sheet1 ..Try the below

In A3
=INDEX(Sheet1!A:A,MATCH(B3,Sheet1!G:G,0))

In C3
=SUMIF(Sheet1!G:G,B3,Sheet1!E:E)

In D3
=INDEX(Sheet1!B:B,MATCH(B3,Sheet1!G:G,0))

If this post helps click Yes
---------------
Jacob Skaria


"Barbara" wrote:

Can somebody figure out this one? I have two worksheets; Worksheet 1 as all
the data and Worksheet 2 is a report that has to be sent to the board.

On Worksheet 2, how to I get a SINGLE entry when I make reference to a Board
# that can repeat itself on Worksheet 1.

Worksheet 1

A B E F G
1 DATE Inv. # Cwt Sales PMB #
2 Amount
3
4 01-Jul-09 27173 300.00 1000.00
5 01-Jul-09 27174 200.00 1000.00 11703
6 01-Jul-09 27175 0.50 1000.00
7 02-Jul-09 27176 1.00 1000.00
8 02-Jul-09 27177 1.50 1000.00
9 02-Jul-09 27178 96 1000.00 11714
10 02-Jul-09 27178 126.00 1000.00 11714
11 02-Jul-09 27178 144.00 1000.00 11714
12 02-Jul-09 27180 25 1000.00

Worksheet 2
A B C D
1 Date Board# CWT Invoice #
2
3 01-Jul-09 11703 200 27174
4 02-Jul-09 11714 366 27178

The formula in my mind would go something like this IN WORKSHEET 2.

In cell A3 to get the date; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 A:A
In cell d3 to get the invoice; IF WORKSHEET1 G:G = WORKSHEET2 B3 THEN RETURN
WORKSHEET1 B:B

The problem occurs when there is more then one instance of the same Board #;
The dates and invoices will always be the same for one board #.

Hope everybody enjoys this problem!