A Antonio Feb 8, 2008 #1 Hi all Is there anyway to extract the contents of the penultimate filled cell Tks in advance
M Mike H Feb 8, 2008 #2 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
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
A Antonio Feb 8, 2008 #3 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
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
T T. Valko Feb 8, 2008 #4 When I try the formulas, they return the last filled cell, and not the penultimate... Click to expand... 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?
When I try the formulas, they return the last filled cell, and not the penultimate... Click to expand... 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 Feb 8, 2008 #5 =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(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
R Ron Rosenfeld Feb 9, 2008 #6 Hi all Is there anyway to extract the contents of the penultimate filled cell Tks in advance Click to expand... 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
Hi all Is there anyway to extract the contents of the penultimate filled cell Tks in advance Click to expand... 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
R Ron Rosenfeld Feb 9, 2008 #7 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 Click to expand... Definition of penultimate: Next to Last Your formulas return the LAST value. --ron
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 Click to expand... Definition of penultimate: Next to Last Your formulas return the LAST value. --ron
R Ron Rosenfeld Feb 9, 2008 #8 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 Click to expand... Actually, Gord's formulas are probably better. --ron
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 Click to expand... Actually, Gord's formulas are probably better. --ron
A Antonio Feb 10, 2008 #9 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 Click to expand...
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 Click to expand...
