I have several cells reading data from a single cell - the last one in a
column - the value of which keeps changing as data is added. How do I ensure
that the several cells read only from the last entry in the column, and not
from older obsolete entries?

This will get you the last value in a column

=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A)))

--

HTH

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


"dihirod" wrote in message
...
I have several cells reading data from a single cell - the last one in a
column - the value of which keeps changing as data is added. How do I
ensure
that the several cells read only from the last entry in the column, and
not
from older obsolete entries?

Many thanks. I stupidly omiteed to add that the list of entries does not
comprise the entire column - only part of it (let's say C5 to c10) after
that, there's oodles of other things in the same column. I'd like to know how
to restrict the lookup to that small section.

"Bob Phillips" wrote:

This will get you the last value in a column

=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A)))

--

HTH

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


"dihirod" wrote in message
...
I have several cells reading data from a single cell - the last one in a
column - the value of which keeps changing as data is added. How do I
ensure
that the several cells read only from the last entry in the column, and
not
from older obsolete entries?

=INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C1
0)))

--

HTH

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


"dihirod" wrote in message
...
Many thanks. I stupidly omiteed to add that the list of entries does not
comprise the entire column - only part of it (let's say C5 to c10) after
that, there's oodles of other things in the same column. I'd like to know
how
to restrict the lookup to that small section.

"Bob Phillips" wrote:

This will get you the last value in a column


=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A)))

--

HTH

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


"dihirod" wrote in message
...
I have several cells reading data from a single cell - the last one in
a
column - the value of which keeps changing as data is added. How do I
ensure
that the several cells read only from the last entry in the column,
and
not
from older obsolete entries?

Many thanks. I tried this myself, but I got, and still get #N/A which I
understand is a MATCH error value. (The first one worked fine as described,
it returned the value from the bottom of the worksheet.) What am I doing
wrong with this one?

"Bob Phillips" wrote:

=INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C10)))

--

HTH

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


"dihirod" wrote in message
...
Many thanks. I stupidly omiteed to add that the list of entries does not
comprise the entire column - only part of it (let's say C5 to c10) after
that, there's oodles of other things in the same column. I'd like to know
how
to restrict the lookup to that small section.

"Bob Phillips" wrote:

This will get you the last value in a column


=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A)))

--

HTH

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


"dihirod" wrote in message
...
I have several cells reading data from a single cell - the last one in
a
column - the value of which keeps changing as data is added. How do I
ensure
that the several cells read only from the last entry in the column,
and
not
from older obsolete entries?

That sounds like you have all numbers or all text.

If all numbers, and always all numbers, use

=INDEX(C5:C10,MAX(MATCH(9.99999999999999E+307,C5:C 10)))

If all text, and always all text, use

=INDEX(C5:C10,MAX(MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5: C10)))

If it could be both, but coul;d be all numbers or all text, use

=INDEX(C5:C10,MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZ ZZZZZZZ"},C5:C10)),MATCH(9
..99999999999999E+307,C5:C10),IF(ISERROR(LOOKUP(9. 99999999999999E+307,C5:C10)
),MATCH("ZZZZZZZZZZZZZZZZZZZZ",C5:C10),MATCH({"ZZZ ZZZZZZZZZZZZZZZZZ",9.99999
999999999E+307},C5:C10)))))



--

HTH

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


"dihirod" wrote in message
...
Many thanks. I tried this myself, but I got, and still get #N/A which I
understand is a MATCH error value. (The first one worked fine as
described,
it returned the value from the bottom of the worksheet.) What am I doing
wrong with this one?

"Bob Phillips" wrote:


=INDEX(C5:C10,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9. 99999999999999E+307},C5:C1
0)))

--

HTH

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


"dihirod" wrote in message
...
Many thanks. I stupidly omiteed to add that the list of entries does
not
comprise the entire column - only part of it (let's say C5 to c10)
after
that, there's oodles of other things in the same column. I'd like to
know
how
to restrict the lookup to that small section.

"Bob Phillips" wrote:

This will get you the last value in a column



=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.999 99999999999E+307},A:A)))

--

HTH

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


"dihirod" wrote in message
...
I have several cells reading data from a single cell - the last
one in
a
column - the value of which keeps changing as data is added. How
do I
ensure
that the several cells read only from the last entry in the
column,
and
not
from older obsolete entries?

If you want to retieve the last numeric value from C5:C10, invoke:

=LOOKUP(9.99999999999999E+307,$C$5:$C$10)

If you want to retieve the last text value from C5:C10, invoke:

=LOOKUP(REPT("z",255),$C$5:$C$10)

The latter will fetch a formula-blank (created by, say, someting like
="") if this text value is the last entry.

dihirod wrote:
Many thanks. I stupidly omiteed to add that the list of entries does not
comprise the entire column - only part of it (let's say C5 to c10) after
that, there's oodles of other things in the same column. I'd like to know how
to restrict the lookup to that small section.

"Bob Phillips" wrote:


This will get you the last value in a column

=INDEX(A:A,MAX(MATCH({"ZZZZZZZZZZZZZZZZZZZZ",9.9 9999999999999E+307},A:A)))

--

HTH

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


"dihirod" wrote in message
...

I have several cells reading data from a single cell - the last one in a
column - the value of which keeps changing as data is added. How do I

ensure

that the several cells read only from the last entry in the column, and

not

from older obsolete entries?




--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.