Find first non #N/A value in either direction

[y3tter]

In Column B1:B14 there are values for relative distance in elevation, which are divided in 14 zones. If you are moving down in elevation the #N/A's will be at the top zones, if you begin moving up in elevation #N/A's will be at the bottom. Basically any zone that is not in the direction in which you are moving will be #N/A. How can you find the the first real value that is not a #N/A, by first looking for if the #N/A is at the top or bottom of the column and then returning the first value after the #N/A. Also, if you are in Zone1 moving down there will be no #N/A's and if you are in Zone14 moving up there will be no #N/A's. There shouldn't be any instance of moving up out of Zone1 or below Zone14.

Below is an example of the 14 zones, the second column would be moving down in elevation while being in Zone6, and the third column is an example of moving up and being in Zone10.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Zone1[/TD]
[TD]#N/A[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]Zone2[/TD]
[TD]#N/A[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD]Zone3[/TD]
[TD]#N/A[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]Zone4[/TD]
[TD]#N/A[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Zone5[/TD]
[TD]#N/A[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]Zone6[/TD]
[TD]10[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]Zone7[/TD]
[TD]10[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Zone8[/TD]
[TD]22[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Zone9[/TD]
[TD]25[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Zone10[/TD]
[TD]35[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Zone11[/TD]
[TD]42[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Zone12[/TD]
[TD]56[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Zone13[/TD]
[TD]66[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Zone14[/TD]
[TD]78[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[y3tter]

That worked, but unfortunately the workbook has VBA that not only deletes the previous set of data but also the table when a new sheet is created. The workbook has a password and I don't have access to it.

Would it be possible to look at column B1:B14 and see if it is going in sequential order, has less than 14 values, and if so use the indexed value of one cell higher than what is being returned. Otherwise, if it has all 14 values in B1:B14 the formula works great.

 

[y3tter]

Could Offset counta be used to create a dynamic range instead of defining it B1:B14? Both columns A and B will have the same amount of values displayed, even in the instance when there are less than 14 values.