Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default [Click the star to watch this topic] finding 1st populated cell on a vector with va


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.


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default [Click the star to watch this topic] finding 1st populated cell on a vector with va


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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default [Click the star to watch this topic] finding 1st populated cell on a vector with va


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.


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
up to 7 functions? ALex Excel Worksheet Functions 10 April 12th 05 06:42 PM
Can a cell have a drop down list and can also be auto populated Adrian Excel Worksheet Functions 1 March 17th 05 06:05 AM
Finding Cell References Zokess Excel Discussion (Misc queries) 2 February 4th 05 05:52 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 05:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"