Formula to find first, second, third etc. value (range)
 

I got a specific range, lets say A1:A4. The cells could either be empty
or include a number.

I need a formula which finds the first cell that is not empty in range
A1:A4 (and shows the value of the first not empty cell). This formula
should be located in cell B1.

Then cell B2 should find the second not-empty cell in range A1:A4 and
so on.

Thanks in advance guys. I'd rather not use macros for this one.


--
Cello
------------------------------------------------------------------------
Cello's Profile: http://www.excelforum.com/member.php...o&userid=18981
View this thread: http://www.excelforum.com/showthread...hreadid=393305

Try...

B1, copied down:

=IF(ROWS($B$1:B1)<=COUNT($A$1:$A$4),INDEX($A$1:$A$ 4,SMALL(IF($A$1:$A$4<"",ROW($A$1:$A$4)-ROW($A$1)+1),ROWS($B$1:B1))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Cello Wrote:
I got a specific range, lets say A1:A4. The cells could either be empty
or include a number.

I need a formula which finds the first cell that is not empty in range
A1:A4 (and shows the value of the first not empty cell). This formula
should be located in cell B1.

Then cell B2 should find the second not-empty cell in range A1:A4 and
so on.

Thanks in advance guys. I'd rather not use macros for this one.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=393305

This seems to work perfectly! Thanks! Couldn't have done it without you.


--
Cello
------------------------------------------------------------------------
Cello's Profile: http://www.excelforum.com/member.php...o&userid=18981
View this thread: http://www.excelforum.com/showthread...hreadid=393305