[Click the star to watch this topic] finding 1st populated cell on a vector with va

[name]

I would like to be able to lookup the value of the first populated cell
to the right and 1 row below that of the active cell and the first
populated cell to the left and 1 row below that of the active cell.
The active is 1 row right above the range of cells with potentially
populated values, which is only 10 cells in lenth.

Is there a function or combination of functions that can accomplish
this? I would like to do this without using additional rows; however,
if absolutely necessary I'm willing to use one extra row.

I've slugged together a solution to find the value to the left, but in
doing this have used my one extra row and cannot figure out how to
accomplish the lookup to the right, without using another row.

For example, the formula in the 'active cell' would be able to find and
use the 10 and 20 in the following cells:

row 1: blank blank 'active cell'
row 2: 10 blank blank blank 20 blank 15 blank blank 10

Thanks for help.

[Maistrye]

I would like to be able to lookup the value of the first populated cell
to the right and 1 row below that of the active cell and the first
populated cell to the left and 1 row below that of the active cell.
The active is 1 row right above the range of cells with potentially
populated values, which is only 10 cells in lenth.

Is there a function or combination of functions that can accomplish
this? I would like to do this without using additional rows; however,
if absolutely necessary I'm willing to use one extra row.

I've slugged together a solution to find the value to the left, but in
doing this have used my one extra row and cannot figure out how to
accomplish the lookup to the right, without using another row.

For example, the formula in the 'active cell' would be able to find and
use the 10 and 20 in the following cells:

row 1: blank blank 'active cell'
row 2: 10 blank blank blank 20 blank 15 blank blank 10

Thanks for help.

When you say "lookup", what do you mean? Are you just trying to use these two values in some formula in row 1, or are you attempting to use these values for a validation list in a pull down menu?

What happens if there is no defined value on the left or right?

Are you able to use a user-defined function? (ie. create your own) Then, just pass it the range you're looking at, specify whether you want the first value on the left or right, etc.

For your rows limitation, is it possible for you to do the calculation you specify on a separate worksheet, then hide that worksheet? That's one thing I sometimes do if I feel the need to do intermediate calculations without having them appear on the main sheet.

Scott

[name]

Thanks for your response.

The retrieved values will be used in a calculation. For example it could be something as simple as the value of the 1st populated cell to the left subtracted from the value of the 1st populated cell to the right. To answer your other question, if there are no values to the right (by default there is at least one value to the left) then all values will equal the value to the nearest value to the left.

When you talk about a user defined function are you referring to a function built using VBA? I'm looking for an approach not using VBA, but will resort to it if I have to.

Good suggestion about using another hidden sheet. However, I'm trying to stay away from this sort solution that uses additional rows. Yes, I know, many constraints.