+ Reply to Thread
Results 1 to 6 of 6

Possible Formula Display Auto Filter Result in a Cell

  1. #1
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    82

    Question 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.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,394

    Re: Possible Formula Display Auto Filter Result in a Cell

    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.

  3. #3
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    82

    Re: Possible Formula Display Auto Filter Result in a Cell

    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.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,394

    Re: Possible Formula Display Auto Filter Result in a Cell

    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/

  5. #5
    Registered User
    Join Date
    10-22-2018
    Location
    Turkey
    MS-Off Ver
    Office2010
    Posts
    82

    Re: Possible Formula Display Auto Filter Result in a Cell

    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

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,394

    Re: Possible Formula Display Auto Filter Result in a Cell

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 11-27-2015, 11:12 AM
  2. Display formula result if cell is X cells away from text value
    By jimmygig in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2013, 08:30 PM
  3. Filter result display
    By Melysa in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-08-2013, 12:19 AM
  4. Formula in cell but does not display result
    By glamint in forum Excel General
    Replies: 1
    Last Post: 08-17-2012, 02:08 AM
  5. Button to display a formula result in a different cell
    By boogeyman in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 01-23-2011, 07:56 PM
  6. Display result in same cell as formula
    By J Strang in forum Excel General
    Replies: 2
    Last Post: 08-21-2006, 02:40 PM
  7. [SOLVED] Can I make a formula in Excel to display result in same cell?
    By Neiko in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2005, 06:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1