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

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

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

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

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

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