How to grab the value of the first cell of a table after applying a filter.
Hey all,
Hope you got what i want from the title.
I have a table, and then i'm applying a filter, and i need to display the first value of the filtered data from a specific column.
Please check the file attached ... i need the cell C5 to display the first value of the filtered date.
Is that possible ?
Thanks.
with helper column for auto numeration visible rows
=INDEX($C$8:$C$18,MATCH(1,$B$8:$B$18))
In your sample the dates are in order from most recent to furthest past, will they be in order in your actual file?
If so, you can use SUBTOTAL with 104 (MAX) or 105 (MIN) if they are in order. SUBTOTAL with the first argument being 100+ works on the visible cells in a range only, thus not your filtered values.
So if you have your dates sorted, MAX of your filtered list would always be the first cell if you have most recent date at top, or MIN if you have least recent date at top.
In C5
Formula:
=SUBTOTAL(104,Table1[DATE])
Then filter some more recent dates out and see results. If you have oldest date at top of list then use 105 instead.
If your list wont be sorted by date we can find another option
Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.
"I am here to help, not do it for people" -Me
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks