| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return last cell in range
Hi, i need in "F4" a formula that return the last written cell from "P4:BM4"
Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return last cell in range
Hi,
Lookup what? Number =LOOKUP(6.022*10^23,P4:BM4) text =LOOKUP(REPT("z",255),P4:BM4) Mike "puiuluipui" wrote: Hi, i need in "F4" a formula that return the last written cell from "P4:BM4" Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return last cell in range
You can use the following array formula. It will return the value of
the right-most non-blank cell in the range P4:BM4. =OFFSET(P4,0,MAX(COLUMN(P4:BM4)*(P4:BM4<""))-COLUMN(P4),1,1) 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 in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces - Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 17 Apr 2009 04:33:01 -0700, puiuluipui wrote: Hi, i need in "F4" a formula that return the last written cell from "P4:BM4" Can this be done? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return last cell in range
Works Great!
Thanks! "Mike H" a scris: Hi, Lookup what? Number =LOOKUP(6.022*10^23,P4:BM4) text =LOOKUP(REPT("z",255),P4:BM4) Mike "puiuluipui" wrote: Hi, i need in "F4" a formula that return the last written cell from "P4:BM4" Can this be done? Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return last cell in range
Works Great!
Thanks! "Chip Pearson" a scris: You can use the following array formula. It will return the value of the right-most non-blank cell in the range P4:BM4. =OFFSET(P4,0,MAX(COLUMN(P4:BM4)*(P4:BM4<""))-COLUMN(P4),1,1) 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 in the Formula Bar enclosed in curly braces { }. (You do not type the curly braces - Excel includes them automatically.) The formula will not work properly if you do not use CTRL SHIFT ENTER. See http://www.cpearson.com/excel/ArrayFormulas.aspx for lots more information about array formulas. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 17 Apr 2009 04:33:01 -0700, puiuluipui wrote: Hi, i need in "F4" a formula that return the last written cell from "P4:BM4" Can this be done? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return a given value if the value of a cell is in a given range | Excel Worksheet Functions | |||
| return cell within date range | Excel Worksheet Functions | |||
| How to return a value if a range (not a cell) contains a certain v | Excel Worksheet Functions | |||
| Return cell adress for next non-blank cell in a range | Excel Worksheet Functions | |||
| if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions | |||
Linear Mode
