A
Antonio
Hi all
Is there anyway to extract the contents of the penultimate filled cell
Tks in advance
Is there anyway to extract the contents of the penultimate filled cell
Tks in advance
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Mike H
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
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
A
Antonio
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
When I try the formulas, they return the last filled cell, and not the
penultimate... M I missing something???
Tks again
T
T. Valko
When I try the formulas, they return the last filled cell,
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?
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?
G
Gord Dibben
=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
=OFFSET(INDEX(B:B,MATCH(REPT("z",255),B:B)),-1,0) for the penultimate text
value.
Gord Dibben MS Excel MVP
R
Ron Rosenfeld
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
A
Antonio
Tks
Gord Dibben said:=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