Retuyrning a single entry when multiples of the same

[Barbara]

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!

 

[Jacob Skaria]

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

 

[Eduardo]

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]

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