Referencing a Value in variable length columns.

K

Kevin

Hi All, I apologize if this is not the right place, I believe this falls
under teh category if worksheet functions;

I have a SS I would like to reference a cell in a column which changes in
length. In this case, I would like to reference the most current date in a
column.

Is there a simple method for doing this, as the data will routinely be
different lengths in that column.

Further, there is Data on the Horizontal that I would like to reference to
another cell in another sheet that would correspond to this date. Can anyone
give any suggestions as to the best way to accomdate this?

Thank you for any suggestions and taking the time to read.

Regards,
Kevin
 
B

Bob Phillips

The date would be

=MAX($A$1:$A$1000)

the value in say column D corresponding to that would be

=INDEX($D$1:$D$1000,MATCH(MAX($A$1:$A$1000),$A$1:$A$1000,0))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
K

Kevin

Thanks Bob, I will give that a try.

I presume that if the range to be referenced is in another sheet, it would
merely include the Sheet Name, ie:

=MAX('SheetName'!$A$1:$A$1000)

Thanks for the help! :)

Kevin
 
K

Kevin

Hi Bob,

I tried the function below, and it returns a value of "00/01/1900"

A couple things I was wondering if they may be the cause:
1) The column being referenced houses the date in the following format:
01/03/2006 16:00:00

2) The first row contains header text (Although I tried adjusting the range
to where the date starts, and it returns the same result)

3) There are a number of blank cells throughout the range (All celss are
date formatted however)

Thanks again!
Kevin
 
P

Peo Sjoblom

That means that the value is zero but you are using date format, if you
change the format to general it will be zero,
note that blank cells are also seen as zero by excel so if the latest date
corresponds to an empty cell that would explain the result although you
might had applied it incorrectly

--
Regards,

Peo Sjoblom

Portland, Oregon
 
K

Kevin

Thanks Guys, nailed down the problem...

Seems that because the Date field also has the Time attached, it is not
picking up the Date as an actual date, resulting in the Zero value.

I'll tinker and see if there is a way to seperate the date and time into
seperate columns and into a format that Excel recognizes as a proper date and
time.

Thanks for the assistance, it really helped out and I learned something new.

Cheers,
Kevin
 
B

Bob Phillips

Kevin,

That doesn't seem to be the problem to me, a date with time in would still
have a MAX > 0.

Which formula returns the 0, the MAX or the INDEX?

Can u post some data to show the problem?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
K

Kevin

Hi Bob, sorry for teh delay, voting day here ;-)

Here is a data sample (I adjusted the formula accordingly as my data is in
Column B):
Column B:
01/03/2006 16:00:00
01/03/2006 16:15:00
01/03/2006 16:30:00
01/03/2006 16:45:00
01/03/2006 17:00:00
01/03/2006 17:15:00
01/03/2006 17:30:00

The start time will always be in the same cell (B4) but the end time can
change, as the number of records can vary. The formula returning the Zero
value is the MAX one for me. Here is what I have:

"=MAX($B$4:$B$1000)"

Which returns:
"00/01/1900"

My workaround was to use the MAX formula against Column A, which is simply
an auto-number couting the number of data points in Column B (Currently 1 to
68) and then use the INDEX to reference the data in Column B adjacent to the
MAX in B:

"=INDEX($B$4:$B$1500,MATCH(MAX($A$4:$A$1500),$A$4:$A$1500,0))"

This DOES return the MAX date and time, but then I ran into another problem:

I used the same set up as described above to retrieve the MIN date and time,
and then wanted to Subtract MIN from MAX and get a count of the total Hours
and Minutes elapsed (HH:mm) but I can't seem to get it to acknowledge more
than 24 hrs, which is a real possibility for this project (Ie: Start date of
01/03/2006 16:00:00, End date of 01/06/2006 21:00:00)

Hopefully I described this correctly. Currently it's all a bit of a jumble
in my head.

Thanks for all your help Bob, I truly appreciate it!

Cheers,
Kevin
 
B

Bob Phillips

Kevin,

Canadian eh?

I would guess that those numbers are not true date and time but text. Try
this formula

=MAX(--($B$4:$B$1000))

as an array formula, so commit with Ctrl-Shift-Enter5, not just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Oh forgot to mention, with the tmes over 24 hours, format the result cell as
[h]:mm

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
K

Kevin

Hi Bob, thanks, that gives me some options to work with. Still having some
issues with making it all work, but I think that has more to do with the
Operator than the advice or program ;)

Sorry for the delay, some how lost the post.

Thanks again!
Kevin

Bob Phillips said:
Oh forgot to mention, with the tmes over 24 hours, format the result cell as
[h]:mm

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

Kevin said:
Hi Bob, sorry for teh delay, voting day here ;-)

Here is a data sample (I adjusted the formula accordingly as my data is in
Column B):
Column B:
01/03/2006 16:00:00
01/03/2006 16:15:00
01/03/2006 16:30:00
01/03/2006 16:45:00
01/03/2006 17:00:00
01/03/2006 17:15:00
01/03/2006 17:30:00

The start time will always be in the same cell (B4) but the end time can
change, as the number of records can vary. The formula returning the Zero
value is the MAX one for me. Here is what I have:

"=MAX($B$4:$B$1000)"

Which returns:
"00/01/1900"

My workaround was to use the MAX formula against Column A, which is simply
an auto-number couting the number of data points in Column B (Currently 1 to
68) and then use the INDEX to reference the data in Column B adjacent to the
MAX in B:

"=INDEX($B$4:$B$1500,MATCH(MAX($A$4:$A$1500),$A$4:$A$1500,0))"

This DOES return the MAX date and time, but then I ran into another problem:

I used the same set up as described above to retrieve the MIN date and time,
and then wanted to Subtract MIN from MAX and get a count of the total Hours
and Minutes elapsed (HH:mm) but I can't seem to get it to acknowledge more
than 24 hrs, which is a real possibility for this project (Ie: Start date of
01/03/2006 16:00:00, End date of 01/06/2006 21:00:00)

Hopefully I described this correctly. Currently it's all a bit of a jumble
in my head.

Thanks for all your help Bob, I truly appreciate it!

Cheers,
Kevin
Click to expand...
Click to expand...
 
K

Krishnakumar

May be..

=LOOKUP(9.999E+307,B4:B1500)-B4+(B4>LOOKUP(9.999E+307,B4:B1500))

format the cell [h]:mm

HTH
 
Top