Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link to AutoFilter Settings (2003)

    Does anyone know if it is possible to show the settings of an AutoFilter in another cell?

    For example, if I have column A with the following data in rows (1,1,1,2,3,3,4,4,5,5,5,5) and I set the AutoFilter to "=1", it will only show the rows that contain "1". Can I set cell B1 to show that the filter in A1 is set to "=1".

    In my actual situation, I have multiple columns with different AutoFilters set. Some are simple and some are custom, and I'd like to be able to tell at a glance what all of the filters are set to.

    Thanks,
    Joe

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to AutoFilter Settings (2003)

    See Excel User Tip: Displaying AutoFilter criteria on John Walkenbach's site.

  3. #3
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to AutoFilter Settings (2003)

    Your amazing. This works perfectly.

  4. #4
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to AutoFilter Settings (2003)

    Oh No. I just found that these cells do not update when I change the filter unless I edit the cell and press enter.

    Function Key F9 will not update them. Do you know of any automated way to update these cells when the AutoFilter is changed?

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to AutoFilter Settings (2003)

    Try the following:

    1) Insert the line

    Application.Volatile

    at the beginning of the FilterCriteria function. This makes it recalculate whenever the worksheet is recalculated.

    2) In another cell of the worksheet, enter a formula that will be recalculated whenever you change something, for example

    =RAND()

    or

    =NOW()

  6. #6
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to AutoFilter Settings (2003)

    No luck. I addded the "Application.Volatile" to the first line below the Function (just like the Microsoft documentation describes), then I added =NOW() to a different cell in the spreadsheet, but I still have to edit the formula and press enter for it to update.

    Any other ideas?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to AutoFilter Settings (2003)

    Strange. It works for me in the attached workbook. What happens if you change the filter? Are cells A1 and B1 updated automatically?
    (Make sure that you enable macros when you open the workbook)

  8. #8
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to AutoFilter Settings (2003)

    You're right. If I do this in a brand new spreadsheet, it updates properly.

    I don't know why it doesn't work in the spreadsheet with my data.
    The spreadsheet that I am using was generated from an application named Business Warehouse which is an interface to SAP.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to AutoFilter Settings (2003)

    Are you sure that calculation is set to Automatic in this workbook? (See the Calculate tab of Tools | Options...)

  10. #10
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to AutoFilter Settings (2003)

    Yep. It's set to automatic.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to AutoFilter Settings (2003)

    'Fraid I don't know what's causing this then. Any chance you can attach a (stripped down and sanitized/anonymized) copy of the worksheet?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Link to AutoFilter Settings (2003)

    You haven't inserted the line

    Application.Volatile

    in the function. If I do that, the formula updates automatically when I change the filter.

  13. #13
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to AutoFilter Settings (2003)

    Good idea. Attached is the stripped down version.

    Notice that when the AutoFilter is changed in cell A2, the formula in cell A1 does not update automatically. If you go to cell A1 and press F2 and then Enter, it updates.

    Thanks

  14. #14
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to AutoFilter Settings (2003)

    I actually had several problems.

    I had created the function in my PERSONAL.xls file so that I could use it in multiple spreadsheets, but I found that I had copy it to each individual spreadsheet anyway to make it work. When I made the change to the function in the PERSONAL.xls file, I then selected (single clicked) the function in the real spreadsheet and the screen did a refresh and I though I was looking at the one in the actual spreadsheet (since it was selected). I found that I actually have to double click the function in the spreadsheet to make it show up in the editor. (Strange)

    The second issue that I had was that I wasn't using just the formula itself, I had added it to an "IF" statement so that it would report when the filter was set to all:
    =IF(filtercriteria(E110)=""," All",filtercriteria(E110))

    This formula does not update automatically even with the modified function.

    So in the end, I created a cell with the function call and then another cell with the if statement pointing to the cell with the function call.

    Anyway, thank you very much for your help. It is all up and running now.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Link to AutoFilter Settings (2003)

    One thing that can be done to help ensure that changing the filter will cause a recalculation is to add a formula which uses the SUBTOTAL function on the filtered list. Thus if a change to the filter is made this cell must be recalculated so a calculation will run and the volatile functions will be recalculated.

    Without this formula, a change to the filtering may not trigger a calculation and the volatiles may not be updated.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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