Need formula to return first value in a cell that is non empty
 

Hi

I have got a coloumn of IF sentences(=IF(N10=$I$2;K10;" ")) and some
of them return a textstring.
Now I want a new coloumn with just the cells that has a texstring in
them.

The reason for this is that the data is for a drop down list and I do
not want blanks in between the things you can select so I want to
create a new coloumn with no spacings in between the choices.

So if there is a formula that finds and returns the value of the next
non empty cell, could someone help me with that?

Any ideas are welcome, though it cannot be a macro.

Thank you

Need formula to return first value in a cell that is non empty
 

Say your data is in column A in A1 thru A11. In B1 enter:

=if(rows($1:1)<=counta($a$1:$a$100),index($a$1:$a$ 100,small(if($a$1:$a$100<"",row($a$1:$a$100)-min(row($a$1:$a$100))+1),rows($1:1))),"")


and copy down

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

For example A1 thru B11:

horse horse
cat
cat mouse
dog
mouse flea
dog tick
flea elephant
tick


elephant

--
Gary''s Student - gsnu2007L


"Kasper" wrote:

Hi

I have got a coloumn of IF sentences(=IF(N10=$I$2;K10;" ")) and some
of them return a textstring.
Now I want a new coloumn with just the cells that has a texstring in
them.

The reason for this is that the data is for a drop down list and I do
not want blanks in between the things you can select so I want to
create a new coloumn with no spacings in between the choices.

So if there is a formula that finds and returns the value of the next
non empty cell, could someone help me with that?

Any ideas are welcome, though it cannot be a macro.

Thank you

Need formula to return first value in a cell that is non empty
 

Hi Gary's student

Thank you for the help. But I cannot get it to work?
I have tried entering what you wrote: =if(rows($1:1)<=counta($a$1:$a
$100),index($a$1:$a$100,small(if($a$1:$a$100<*"", row($a$1:$a$100)-min
(row($a$1:$a$100))+1),rows($1:1))),"")

in a cell but it gives me an error whenever I press enter and marks
the index part of the formula as if there is an error there?

Any suggestions what the error could be?