name Wrote:
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


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=562402