Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to find and sum values in a different spreadsheet | Excel Worksheet Functions | |||
Formula to find cell with data on a row | Excel Worksheet Functions | |||
Find and Replace Formula Links | Excel Worksheet Functions | |||
Can't find the right lookup formula for this | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions |