[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.

--
Message posted via http://www.officekb.com

[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)


"adeel via OfficeKB.com" <u32736@uwe wrote in message
news:74b850ce1f165@uwe...
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.

--
Message posted via http://www.officekb.com

[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.)



"adeel via OfficeKB.com" wrote:

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.

--
Message posted via http://www.officekb.com

--

Dave Peterson

[Jay Somerset]

On Thu, 5 Jul 2007 10:02:58 -0500, "Chip Pearson"
wrote:

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.
--
Jay (remove dashes for legal email address)

[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

On Thu, 05 Jul 2007 12:23:11 -0400, Jay Somerset wrote:

On Thu, 5 Jul 2007 10:02:58 -0500, "Chip Pearson"
wrote:

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.

[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)



Jay Somerset wrote:

On Thu, 5 Jul 2007 10:02:58 -0500, "Chip Pearson"
wrote:

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.
--
Jay (remove dashes for legal email address)

--

Dave Peterson

[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

Gord Dibben wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

[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

On Sat, 07 Jul 2007 08:11:53 GMT, "adeel via OfficeKB.com" <u32736@uwe wrote:

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

Gord Dibben wrote:
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.