Need help with formula for finding and returning the next non-blank cell in a range. Goal is to populate column B by formula.


A B
1 18246 18260
2 18260 18381
3
4 18381 18394


5
6
7 18394 18441
8 18441 18445
9
10 18445 18709


For each row, I need a formula in column B that pulls the next available number from a subsequent row in column A, regardless of how many blank rows there are between them -- there could be no blanks at all, or there could be many.

Ideally, I'd also like an if-then that says if there's no value in column A for a row, then there's no corresponding value in column B (i.e., leave the column B cell blank) so I can just copy and past the formula down all of column B and not have to got back and delete all the nulls.

I'd really appreciate any suggestions to make this work! It needs to be Excel 2007 compatible.

Thanks