Retreiving value in last cell

Walter Mayes · Apr 14, 2004

Excel 2002

Currently I have values in column G. Last cell used, at this point, is
G86.
Values are added periodically so that next week the last cell value may
be in G93.
What I would like to accomplish is: In cell S4 I would like to pick
up the last value in column G.
i.e. today S4 would equal G86 Next week S4 would equal G93.
Last value in column G "may" not be the highest nor the lowest value in
the range.
Hopefully I can do this with functions as I have not graduated to macros
or VBA, at this point.

TIA
Walter Mayes

Aladin Akyurek · Apr 14, 2004

It looks like you're interested in the last numeric value. If so:

=LOOKUP(9.99999999999999E+307,G:G)

Frank Kabel · Apr 14, 2004

Hi
to get the last entry in a column use one of the following formulas:

0. if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))

Dave R. · Apr 14, 2004

=MAX(IF(G1:G9999<>"",ROW(G1:G9999)))

press CTRL SHIFT ENTER after pasting this formula in, and will appear as

{=MAX(IF(G1:G9999<>"",ROW(G1:G9999)))}

automatically.

Dave R. · Apr 14, 2004

Sorry, that wouldn't give you the actual value, this will;

=INDEX(G1:G9999,MAX(IF(G1:G9999<>"",ROW(G1:G9999))))

enter with control shift enter.

Aladin Akyurek · Apr 14, 2004

Frank Kabel said:
Hi
to get the last entry in a column use one of the following formulas:

0. if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

COUNTA is slower than MATCH with match-type set to 1 for it invokes binary
search.

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(A:A,MATCH(REPT(CHAR(255),255,A:A))

This is risky. You probably meant: REPT("z",255).

2. If you have only numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E300,A:A))

Also:

=LOOKUP(9.99999999999999E+307,A:A)

Someone who is a tad more lazy than I'm suggests:

10^308 instead of the constant 9.99999999999999E+307.

Of course, one can also define BigNum as referring to 9.99999999999999E+307
and use BigNum in the formulas.

3. If you have both (text and values)
=INDEX(A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

The REPT bit notwithstanding, this formula requires advance knowledge that
the reference indeed has at least one text value and one numeric value.

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))

This one must be invoked if one wants any last entry, not when one is
interested in either the last numeric or the last text value.

[...]

Frank Kabel · Apr 14, 2004

Hi Aladin

see below

COUNTA is slower than MATCH with match-type set to 1 for it invokes
binary search.

Thanks for the info

This is risky. You probably meant: REPT("z",255).

why is this risky. I though using 'z' could be risky if you have other
characters in the cells. But this could be my misunderstanding

Also:

=LOOKUP(9.99999999999999E+307,A:A)

Someone who is a tad more lazy than I'm suggests:

10^308 instead of the constant 9.99999999999999E+307.

Of course, one can also define BigNum as referring to
9.99999999999999E+307 and use BigNum in the formulas.

The REPT bit notwithstanding, this formula requires advance knowledge
that the reference indeed has at least one text value and one numeric
value.

Agree: Should have been more specific explaining this

This one must be invoked if one wants any last entry, not when one is
interested in either the last numeric or the last text value.

Correct

Frank

Walter Mayes · Apr 14, 2004

Thank you Aladin. Works exactly as needed. LOOKUP never entered my mind.

Thanks also to Frank and Dave. Have printed your responses and am sure
they will come in handy in the future.

THANKS
Walter Mayes

Aladin Akyurek · Apr 14, 2004

[...]

why is this risky. I though using 'z' could be risky if you have other
characters in the cells. But this could be my misunderstanding

Easy to test.

Let column A house from A1 on:

{"";"u";"i";"m";"z";"n";"";"z";"x"}

"" stands for an empty cell. After last "z" all cells are empty.

=LOOKUP(REPT("z",255),A:A)

will return "x", which is correct, while

=LOOKUP(REPT(CHAR(255),255),A:A)

returns "n".

The string of 255 "ÿ" [ whic is: CHAR(255) ] is not the lexically latest
item, while a string of 255 "z" is.

[...]

Frank Kabel · Apr 14, 2004

Aladin said:
[...]

Easy to test.

Let column A house from A1 on:

{"";"u";"i";"m";"z";"n";"";"z";"x"}

"" stands for an empty cell. After last "z" all cells are empty.

=LOOKUP(REPT("z",255),A:A)

will return "x", which is correct, while

=LOOKUP(REPT(CHAR(255),255),A:A)

returns "n".

The string of 255 "ÿ" [ whic is: CHAR(255) ] is not the lexically
latest item, while a string of 255 "z" is.

[...]

Hi Aladin
Thanks for this. And yes, really easy to test (sorry for beeing
lazy...)

Wish you a nice evening
Frank

Guest · Apr 14, 2004

I wanted to do something similar, so I used your advice and modified it to reference to another worksheet within the same workbook, so I didn't have to manually copy over my value from the itemization page to the others. Here is my modified example.
on page citibank I had been calculating the amount of interest saved by paying extra on the principle. Which would add a new entry to the column each month.
on my accounts page, I wanted to carry over the value of the latest remaining principle.

so I simply modified your formula suggestion and place it into the appropriate cell on the accounts page.
=LOOKUP(1000000000000,'citibank '!B:B) obviously the value of my remaining principle was in column B on page "citibank".

Thank you for your advice to Walter

----- Aladin Akyurek wrote: ----

It looks like you're interested in the last numeric value. If so

=LOOKUP(9.99999999999999E+307,G:G

Aladin Akyurek · Apr 15, 2004

Ben Ward said:
I wanted to do something similar, so I used your advice and modified it to

reference to another worksheet within the same workbook, so I didn't have to
manually copy over my value from the itemization page to the others. Here is
my modified example.

on page citibank I had been calculating the amount of interest saved by

paying extra on the principle. Which would add a new entry to the column
each month.

on my accounts page, I wanted to carry over the value of the latest remaining principle.

so I simply modified your formula suggestion and place it into the

appropriate cell on the accounts page.

=LOOKUP(1000000000000,'citibank '!B:B) obviously the value of my

remaining principle was in column B on page "citibank".[...]

Ben,

Not sure I'm picking out the right intent. If you want the last numeric
value from column B from sheet 'citibank ', it's safer to use Excel's max
numeric constant:

=LOOKUP(9.99999999999999E+307,'citibank '!B:B)

Aladin

Retreiving value in last cell

Walter Mayes

Aladin Akyurek

Frank Kabel

Dave R.

Dave R.

Aladin Akyurek

Frank Kabel

Walter Mayes

Aladin Akyurek

Frank Kabel

Guest

Aladin Akyurek