Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

Hi,
In a macro use
penultimatevalue = Cells(Rows.Count, "A").End(xlUp)

On a worksheet for text use
=INDEX(B:B,MATCH(REPT("z",10),B:B,1),1)

for a number number
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

if you don't know if its a number or text
=INDIRECT("B"&MAX(IF(NOT(ISBLANK(B1:B100)),ROW(1:1 00))))
Which is an array so Ctrl+Shift+Enter

Mike

"Antonio" wrote:

Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

Tks for the help....

When I try the formulas, they return the last filled cell, and not the
penultimate... M I missing something???

Tks again

"Mike H" wrote:

Hi,
In a macro use
penultimatevalue = Cells(Rows.Count, "A").End(xlUp)

On a worksheet for text use
=INDEX(B:B,MATCH(REPT("z",10),B:B,1),1)

for a number number
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

if you don't know if its a number or text
=INDIRECT("B"&MAX(IF(NOT(ISBLANK(B1:B100)),ROW(1:1 00))))
Which is an array so Ctrl+Shift+Enter

Mike

"Antonio" wrote:

Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

=OFFSET(INDEX(B:B,MATCH(99^99,B:B)),-1,0) for the penultimate numeric value

=OFFSET(INDEX(B:B,MATCH(REPT("z",255),B:B)),-1,0) for the penultimate text
value.


Gord Dibben MS Excel MVP

On Fri, 8 Feb 2008 06:23:01 -0800, Antonio
wrote:

Tks for the help....

When I try the formulas, they return the last filled cell, and not the
penultimate... M I missing something???

Tks again

"Mike H" wrote:

Hi,
In a macro use
penultimatevalue = Cells(Rows.Count, "A").End(xlUp)

On a worksheet for text use
=INDEX(B:B,MATCH(REPT("z",10),B:B,1),1)

for a number number
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

if you don't know if its a number or text
=INDIRECT("B"&MAX(IF(NOT(ISBLANK(B1:B100)),ROW(1:1 00))))
Which is an array so Ctrl+Shift+Enter

Mike

"Antonio" wrote:

Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

Tks


"Gord Dibben" wrote:

=OFFSET(INDEX(B:B,MATCH(99^99,B:B)),-1,0) for the penultimate numeric value

=OFFSET(INDEX(B:B,MATCH(REPT("z",255),B:B)),-1,0) for the penultimate text
value.


Gord Dibben MS Excel MVP

On Fri, 8 Feb 2008 06:23:01 -0800, Antonio
wrote:

Tks for the help....

When I try the formulas, they return the last filled cell, and not the
penultimate... M I missing something???

Tks again

"Mike H" wrote:

Hi,
In a macro use
penultimatevalue = Cells(Rows.Count, "A").End(xlUp)

On a worksheet for text use
=INDEX(B:B,MATCH(REPT("z",10),B:B,1),1)

for a number number
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

if you don't know if its a number or text
=INDIRECT("B"&MAX(IF(NOT(ISBLANK(B1:B100)),ROW(1:1 00))))
Which is an array so Ctrl+Shift+Enter

Mike

"Antonio" wrote:

Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

On Fri, 8 Feb 2008 04:26:04 -0800, Mike H
wrote:

Hi,
In a macro use
penultimatevalue = Cells(Rows.Count, "A").End(xlUp)

On a worksheet for text use
=INDEX(B:B,MATCH(REPT("z",10),B:B,1),1)

for a number number
=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

if you don't know if its a number or text
=INDIRECT("B"&MAX(IF(NOT(ISBLANK(B1:B100)),ROW(1: 100))))
Which is an array so Ctrl+Shift+Enter

Mike


Definition of penultimate: Next to Last

Your formulas return the LAST value.
--ron

When I try the formulas, they return the last filled cell,
and not the penultimate...

Which formula did you try?

How about giving us some more detailed info, like:

Where to look for this value. Is it in the range A1:A100? Is it in the range
B10:AJ10?

What type of data are we looking for? Is it a text value? Is it a numeric
value?

Will the range to look in contain only one data type? Is it text or is it
numeric? Can it be both?

Are there any empty/blank cells in the range we need to look in?

Are there any formulas in this range that return blank cells?


--
Biff
Microsoft Excel MVP


"Antonio" wrote in message
...
Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

On Fri, 8 Feb 2008 04:01:00 -0800, Antonio
wrote:

Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

Yes there is. But it depends on how your data is organized.

If your data is in column A, is contiguous (no blank spaces), then:

=OFFSET(A1,COUNTA(A:A)-2,0)

If your data is organized differently, then the method will be different.
--ron

On Fri, 08 Feb 2008 19:27:12 -0500, Ron Rosenfeld
wrote:

On Fri, 8 Feb 2008 04:01:00 -0800, Antonio
wrote:

Hi all

Is there anyway to extract the contents of the penultimate filled cell

Tks in advance

Yes there is. But it depends on how your data is organized.

If your data is in column A, is contiguous (no blank spaces), then:

=OFFSET(A1,COUNTA(A:A)-2,0)

If your data is organized differently, then the method will be different.
--ron

Actually, Gord's formulas are probably better.
--ron