LinkBack URL
About LinkBacks
Hi All,
As per check in my attached excel file I am searching for a possible formula displaying the result in a cell depending filter criteria. More information is given at my example file.
Thanks in advance for your kindest prompts.
Possible Formula Display Auto Filter Result in a Cell
Hi All,
As per check in my attached excel file I am searching for a possible formula displaying the result in a cell depending filter criteria. More information is given at my example file.
Thanks in advance for your kindest prompts.
Hi zrs,
How about using a Slicer instead of trying to use a merged cell to display what is used as the filter? See the attached:
Slicer Example for Hotel Choice.xlsx
https://chandoo.org/wp/introduction-to-slicers/
Excel has the slicer tool which is very similar to what you want. I'd suggest you use its tools instead of trying to build your own special features similar to what they do already.
Last edited by MarvinP; 03-17-2020 at 07:40 PM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Dear Marvin,thanks for your suggestion, I will evaluate it in my future works but this workbook is going to be used by staff and I need a proper formula to show as in the filter. I can unmerge the D5:F5 and this array formula on D5 {=INDEX(E7:E12,MIN(IF(SUBTOTAL(3,OFFSET(E7,ROW(E7:E12)-ROW(E7),0)),ROW(E7:E12)-ROW(E7)+1)))} can display what I need but not write "ALL" when no filter applied. Regards
By the way Slicer does not work in Excel 2010
Last edited by zrs; 03-18-2020 at 06:29 AM.
Hey zrs,
I've written some event VBA and put it on a double click on your location filter. See if this might be an answer. Will you accept a VBA solution? What if two different locations are selected, like London AND Berlin? What should the yellow box display?
Count Hidden Cells to Display Filter Event.xlsm
I found my VBA suggestion at: https://www.mrexcel.com/board/thread...-range.789477/
Hi Marvin,
Well your suggestion can be a nice solution but I must deploy the file on .xlsx format. Some of staff are strictly forbidden to use files with macro (I do not know why?). However I found another formula part in internet and combine with my previous. Now like this: {=IF(COUNTA(E7:E12)>SUBTOTAL(3,E7:E12),INDEX(E7:E12,MIN(IF(SUBTOTAL(3,OFFSET(E7,ROW(E7:E12)-ROW(E7),0)),ROW(E7:E12)-ROW(E7)+1))),"ALL")} . Formula gives appropriate result upon filter (must be unmerged put it D5) but can not display both items (Eg. London, Berlin) if more than one item selected showing the top cell value only. Still searching
Sorry but I don't know how to put "All" in that cell without using some VBA and the hidden property. I don't think hidden is available using normal Excel Formulas.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks