Next Value

[twistinginthewind]

I have a worksheet with Column A with 5 rows (three are blank) See below.
A
1 Test
2 {blank}
3 {blank}
4 {blank}
5 Test 2

What I would like is a formula I can put in column B that would tell me which row contains the next item in column A. so it would looks omthing like:

A B
1 Test 5
2 {blank}
3 {blank}
4 {blank}
5 Test 2

I want to be able to do this no matter how many blanks there are or how many items.

Any ideas?

 

[PaddyD]

assuming your table started in row 1 - column headings in row 1, data from row 2:

Book1
	A	B	C	D
1	Data	Next hit
2	1	5
3
4
5	2	7
6
7	3	11
8
9
10
11	4	12
12	5	12
Sheet1

formula is:

=IF(LEN(A2),MIN(IF(LEN(A3:$A$12)>0,ROW(A3:$A$12))),"")

...which needs to be entered with control + shift + enter, not just enter

 

[Yogi Anand]

Or ... in line with Paddy'e thinking ...

Book1
	A	B	C	D
1	Data	NextHit
2	1	5
3
4
5	2	7
6
7	3	11
8
9
10
11	4	13
12
13	5
Sheet3

array formula in cell B2 is ... =MIN(IF(A3:$A$65536="","",ROW(A3:$A$65536)))