RETURN THE LAST FILLED CELL VALUE

[adeel via OfficeKB.com]

i have the range of numeric values in cloumn (lets assume "C"), in this
column some cells are empty and some are filled. i want to know a function or
set of functions that return the LAST FILLED CELL VALUE from COLUMN "C". hope
my question is clear & some one have right solution of it.

Thanks.

 

[Chip Pearson]

Use a formula like the following array formula:

=MAX((C1:C1000<>"")*C1:C1000)

Since this is an array formula, you must press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }. For more information about array formulas, see
www.cpearson.com/excel/array.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Dave Peterson]

One way:

=LOOKUP(2,1/(A1:A999<>""),A1:A999)
(Make that 999 big enough to extend past the last possible row--but don't use
the whole column.)

 

[Jay Somerset]

Use a formula like the following array formula:

=MAX((C1:C1000<>"")*C1:C1000)

Since this is an array formula, you must press CTRL+SHIFT+ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }. For more information about array formulas, see
www.cpearson.com/excel/array.htm .

I was intrigued by your formula, so I tried it. For me, it returns the
largest value in the range, not the last entered. If you replace MAX
with MIN, it returns the smallest.

How is your formula supposed to work? I can't figure out the logic.
Thanks.

 

[Gord Dibben]

Jay

Try this formula to get last filled cell(numeric or text) in column A

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I think Chip's fingers worked too fast--and his eyes, too <vbg>.

I'm guessing that he wanted to give you the row of the last used cell in that
column:

=MAX((C1:C1000<>"")*row(C1:C1000))

(Used cell = non-blank looking cell)

 

[adeel via OfficeKB.com]

Thanks alot Mr. Dibben your Formula Help is perfect. and I have find the
solution with this.
Now kindly explain me this formula, that what logic you use. i will be highly
thankful to you.

adeel

Jay

Try this formula to get last filled cell(numeric or text) in column A

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

Gord Dibben MS Excel MVP
[quoted text clipped - 12 lines]

How is your formula supposed to work? I can't figure out the logic.
Thanks.

 

[Gord Dibben]

Bob Phillips' with the help of the late Frank Kabel explains this formula and
many others for getting the last cell in a range.

http://xldynamic.com/source/xld.LastValue.html#S012


Gord Dibben MS Excel MVP

Thanks alot Mr. Dibben your Formula Help is perfect. and I have find the
solution with this.
Now kindly explain me this formula, that what logic you use. i will be highly
thankful to you.

adeel

Jay

Try this formula to get last filled cell(numeric or text) in column A

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

Gord Dibben MS Excel MVP

Use a formula like the following array formula:

[quoted text clipped - 12 lines]

How is your formula supposed to work? I can't figure out the logic.
Thanks.