When using this formula '=INDEX($A$1:$A$16,MATCH(MAX($B$1:$B$16),$B$1:$B$16,0))' to return the value in a cell to the left of a cell that is the max or min of a range of data...I need to be able to filter the data so that I return the value I need only from the revealed data, not from what is hidden after the filter is applied. Need help please!
Thanks!
MAX will search all rows, hidden/filtered or not.
Please Login or Register to view this content.
The SUBTOTAL function performs other functions -- "4" is "MAX" -- but it does ignore hidden and filtered rows, so it should do what you want. But note: it only works on rows, such as B1:B16, not columns, such as A1:N1.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks