G
Guest
I need a formula that will return the last entry in a column.
R
Ron de Bruin
Try this one for column C, only numbers
=LOOKUP(9.99999999999999E+307,C:C)
For text and numbers
this is a array formula (Instead of enter hit Ctrl-Shift-enter)
=INDEX(C:C,MAX(ROW(1:65535)*(C1:C65535<>"")))
=LOOKUP(9.99999999999999E+307,C:C)
For text and numbers
this is a array formula (Instead of enter hit Ctrl-Shift-enter)
=INDEX(C:C,MAX(ROW(1:65535)*(C1:C65535<>"")))
F
Frank Kabel
Hi Sheri
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(Entry!A:A,MATCH(REPT(CHAR(255),255,A:A))
2. If you have only numbers in column A:
=INDEX(Entry!A:A,MATCH(9.99999999999999E300,A:A))
3. If you have both (text and values)
=INDEX(Entry!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))))
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(Entry!A:A,MATCH(REPT(CHAR(255),255,A:A))
2. If you have only numbers in column A:
=INDEX(Entry!A:A,MATCH(9.99999999999999E300,A:A))
3. If you have both (text and values)
=INDEX(Entry!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))))
N
Norman Harker
Hi Frank!
Your Formula 1 is in error:
I think you want:
=INDEX(A:A,MATCH(REPT(CHAR(255),255),A:A))
or if in a different sheet:
=INDEX(Entry!A:A,MATCH(REPT(CHAR(255),255),Entry!A:A))
In case you think I'm checking all your posts. No! Just updating my
solutions text by stealing some of your posts; but I'm putting it down
as "research"!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Your Formula 1 is in error:
I think you want:
=INDEX(A:A,MATCH(REPT(CHAR(255),255),A:A))
or if in a different sheet:
=INDEX(Entry!A:A,MATCH(REPT(CHAR(255),255),Entry!A:A))
In case you think I'm checking all your posts. No! Just updating my
solutions text by stealing some of your posts; but I'm putting it down
as "research"!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
F
Frank Kabel
Hi Norman
no problem with corrections. I also use my old mails and sometimes an
error just stays on. So I also will update my solution text -> thanks
for the correction
no problem with corrections. I also use my old mails and sometimes an
error just stays on. So I also will update my solution text -> thanks
for the correction
J
JMay
Ron - Test driving the below formula points out the need to allow for
someone entering the <<spacebar>> (once or twice) somewhere below
the actual real value. How can this wrinkle be overcome? Entering the (')
apostrophe
doesn't affect things...
TIA,
JMay
someone entering the <<spacebar>> (once or twice) somewhere below
the actual real value. How can this wrinkle be overcome? Entering the (')
apostrophe
doesn't affect things...
TIA,
JMay
A
Aladin Akyurek
I suppose you're addressing the array-formula. You want it to ignore the
apacebar entries, right?
apacebar entries, right?
J
JMay
Yes Aladin, sorry I didn't specify..
Aladin Akyurek said: