Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Reference? Question
I've Excel 2000 and I've a (little?) problem.
I have a sheet like below (example): Sheet name: Invoer A B C 1 1 bal bol 2 2 bol kat 3 3 kat bal 4 1 kat bol 5 2 bol jan 6 2 bal jan 7 3 jan bol 8 3 bal kat 9 3 jan kat 10 1 bol jol Now, what I'm looking for is a formula in a new sheet what gives me on [newsheet row 1] the words in [invoer column B] where the value in [invoer column A]=1, and on [newsheet row 2] the words in [invoer column C] where the value in [invoer column A]=1. The result in the newsheet must be like this (based on example): Sheet name: NewSheet A B C 1 bal kat bol 2 bol bol jol I hope someone can help me with this. Thanks, Remco |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Reference? Question
In A1 put in
=IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$B$1:$B$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. in B1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$C$1:$C$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. Select A1:B1 and drag fill to the right. Gave me the values you showed. -- Regards, Tom Ogilvy Remco wrote in message ... I've Excel 2000 and I've a (little?) problem. I have a sheet like below (example): Sheet name: Invoer A B C 1 1 bal bol 2 2 bol kat 3 3 kat bal 4 1 kat bol 5 2 bol jan 6 2 bal jan 7 3 jan bol 8 3 bal kat 9 3 jan kat 10 1 bol jol Now, what I'm looking for is a formula in a new sheet what gives me on [newsheet row 1] the words in [invoer column B] where the value in [invoer column A]=1, and on [newsheet row 2] the words in [invoer column C] where the value in [invoer column A]=1. The result in the newsheet must be like this (based on example): Sheet name: NewSheet A B C 1 bal kat bol 2 bol bol jol I hope someone can help me with this. Thanks, Remco |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Reference? Question
I think there's atypo; the second formula should go in A2 rather than B1.
Alan Beban Tom Ogilvy wrote: In A1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$B$1:$B$10,SMALL(IF(Invoer!$A$1:$A$10=1,ROW( $A$1:$A$10)),COLUMN()),1),"") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. in B1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$C$1:$C$10,SMALL(IF(Invoer!$A$1:$A$10=1,ROW( $A$1:$A$10)),COLUMN()),1),"") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. Select A1:B1 and drag fill to the right. Gave me the values you showed. -- Regards, Tom Ogilvy Remco wrote in message ... I've Excel 2000 and I've a (little?) problem. I have a sheet like below (example): Sheet name: Invoer A B C 1 1 bal bol 2 2 bol kat 3 3 kat bal 4 1 kat bol 5 2 bol jan 6 2 bal jan 7 3 jan bol 8 3 bal kat 9 3 jan kat 10 1 bol jol Now, what I'm looking for is a formula in a new sheet what gives me on [newsheet row 1] the words in [invoer column B] where the value in [invoer column A]=1, and on [newsheet row 2] the words in [invoer column C] where the value in [invoer column A]=1. The result in the newsheet must be like this (based on example): Sheet name: NewSheet A B C 1 bal kat bol 2 bol bol jol I hope someone can help me with this. Thanks, Remco |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Reference? Question
absolutely - my mistake - thanks for the heads up.
Regards, Tom Ogilvy Alan Beban wrote in message ... I think there's atypo; the second formula should go in A2 rather than B1. Alan Beban Tom Ogilvy wrote: In A1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$B$1:$B$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. in B1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$C$1:$C$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. Select A1:B1 and drag fill to the right. Gave me the values you showed. -- Regards, Tom Ogilvy Remco wrote in message ... I've Excel 2000 and I've a (little?) problem. I have a sheet like below (example): Sheet name: Invoer A B C 1 1 bal bol 2 2 bol kat 3 3 kat bal 4 1 kat bol 5 2 bol jan 6 2 bal jan 7 3 jan bol 8 3 bal kat 9 3 jan kat 10 1 bol jol Now, what I'm looking for is a formula in a new sheet what gives me on [newsheet row 1] the words in [invoer column B] where the value in [invoer column A]=1, and on [newsheet row 2] the words in [invoer column C] where the value in [invoer column A]=1. The result in the newsheet must be like this (based on example): Sheet name: NewSheet A B C 1 bal kat bol 2 bol bol jol I hope someone can help me with this. Thanks, Remco |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Reference? Question
Tried to do it, but unfortunately Excel gives me an error:
"De formula you typed contains an error" And than it highlited the first [$A$10,1] in the formula. Can it be my version of excell or did I do something wrong? Remco -- Tom Ogilvy wrote in message ... In A1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$B$1:$B$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. in B1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$C$1:$C$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. Select A1:B1 and drag fill to the right. Gave me the values you showed. -- Regards, Tom Ogilvy Remco wrote in message ... I've Excel 2000 and I've a (little?) problem. I have a sheet like below (example): Sheet name: Invoer A B C 1 1 bal bol 2 2 bol kat 3 3 kat bal 4 1 kat bol 5 2 bol jan 6 2 bal jan 7 3 jan bol 8 3 bal kat 9 3 jan kat 10 1 bol jol Now, what I'm looking for is a formula in a new sheet what gives me on [newsheet row 1] the words in [invoer column B] where the value in [invoer column A]=1, and on [newsheet row 2] the words in [invoer column C] where the value in [invoer column A]=1. The result in the newsheet must be like this (based on example): Sheet name: NewSheet A B C 1 bal kat bol 2 bol bol jol I hope someone can help me with this. Thanks, Remco |
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Reference? Question
My formula (copied out of a worksheet where it is working fine) is set up
for US English. If you are not using US English, you may have to do some translating. In US English, the comma (,) is a list separator, but in some other countries, the semicolon (;) is used as a list separator . If you would like to post your email address I can email you the worksheet and when you open it, excel will do the translations. -- Regards, Tom Ogilvy Remco wrote in message ... Tried to do it, but unfortunately Excel gives me an error: "De formula you typed contains an error" And than it highlited the first [$A$10,1] in the formula. Can it be my version of excell or did I do something wrong? Remco -- Tom Ogilvy wrote in message ... In A1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$B$1:$B$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. in B1 put in =IF(COUNTIF(Invoer!$A$1:$A$10,1)=COLUMN(),INDEX(I nvoer!$C$1:$C$10,SMALL(IF( Invoer!$A$1:$A$10=1,ROW($A$1:$A$10)),COLUMN()),1), "") enter this with Ctrl+shift+Enter rather than just enter since it is an array formula. Select A1:B1 and drag fill to the right. Gave me the values you showed. -- Regards, Tom Ogilvy Remco wrote in message ... I've Excel 2000 and I've a (little?) problem. I have a sheet like below (example): Sheet name: Invoer A B C 1 1 bal bol 2 2 bol kat 3 3 kat bal 4 1 kat bol 5 2 bol jan 6 2 bal jan 7 3 jan bol 8 3 bal kat 9 3 jan kat 10 1 bol jol Now, what I'm looking for is a formula in a new sheet what gives me on [newsheet row 1] the words in [invoer column B] where the value in [invoer column A]=1, and on [newsheet row 2] the words in [invoer column C] where the value in [invoer column A]=1. The result in the newsheet must be like this (based on example): Sheet name: NewSheet A B C 1 bal kat bol 2 bol bol jol I hope someone can help me with this. Thanks, Remco |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell reference question | Excel Worksheet Functions | |||
reference question | Excel Discussion (Misc queries) | |||
Cell Reference Question | Excel Worksheet Functions | |||
Reference question | Excel Worksheet Functions | |||
Cell reference question | Excel Discussion (Misc queries) |