|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 2nd, 2009, 02:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! 
|
|
#2
October 2nd, 2009, 02:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#3
October 2nd, 2009, 03:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#4
October 2nd, 2009, 03:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
