Referencing a Value in variable length columns.

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

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)

"Kevin" <[email protected]> wrote in message
news:[email protected]...
> 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

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

"Bob Phillips" wrote:

> 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)
>
> "Kevin" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

"Bob Phillips" wrote:

> 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)
>
> "Kevin" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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




"Kevin" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Bob Phillips" wrote:
>
>> 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)
>>
>> "Kevin" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>>
>>
>>

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

"Peo Sjoblom" wrote:

> 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
>
>
>
>
> "Kevin" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Bob Phillips" wrote:
> >
> >> 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)
> >>
> >> "Kevin" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>

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)

"Kevin" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Peo Sjoblom" wrote:
>
> > 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
> >
> >
> >
> >
> > "Kevin" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > > "Bob Phillips" wrote:
> > >
> > >> 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)
> > >>
> > >> "Kevin" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > 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
> > >>
> > >>
> > >>
> >
> >