Previous visible cell
Hi,
I have a worksheet using an autofilter and I need to get the value of the previous visible cell once the filter is applied. Assuming my formula is in cell A1000 then is there a worksheet function(s) that can give me the value of the previous visible cell which will change depending on what filter criteria I have. Thanks for any help Fred |
Previous visible cell
Fred,
In an otherwise blank column, insert a formula like this (in cell A2, for example, column B being part of the filtered range): =SUBTOTAL(3,$B$1:B2) and then copy down to match column B. Then, to get the last filtered item from column B, use =VLOOKUP(MAX(A1:A19),A1:B1000,2,FALSE) The second to last, use =VLOOKUP(MAX(A1:A19)-1,A1:B1000,2,FALSE) etc. HTH, Bernie MS Excel MVP "Fred" <leavemealone@home wrote in message ... Hi, I have a worksheet using an autofilter and I need to get the value of the previous visible cell once the filter is applied. Assuming my formula is in cell A1000 then is there a worksheet function(s) that can give me the value of the previous visible cell which will change depending on what filter criteria I have. Thanks for any help Fred |
Previous visible cell
Thanks Bernie,
Worked well.. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Fred, In an otherwise blank column, insert a formula like this (in cell A2, for example, column B being part of the filtered range): =SUBTOTAL(3,$B$1:B2) and then copy down to match column B. Then, to get the last filtered item from column B, use =VLOOKUP(MAX(A1:A19),A1:B1000,2,FALSE) The second to last, use =VLOOKUP(MAX(A1:A19)-1,A1:B1000,2,FALSE) etc. HTH, Bernie MS Excel MVP "Fred" <leavemealone@home wrote in message ... Hi, I have a worksheet using an autofilter and I need to get the value of the previous visible cell once the filter is applied. Assuming my formula is in cell A1000 then is there a worksheet function(s) that can give me the value of the previous visible cell which will change depending on what filter criteria I have. Thanks for any help Fred |
All times are GMT +1. The time now is 04:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com