+ Reply to Thread
Results 1 to 12 of 12

Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

  1. #1
    Registered User
    Join Date
    10-02-2020
    Location
    Woodbury, MN
    MS-Off Ver
    365
    Posts
    10

    Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    Hello,

    Using a set of 5 different menu drop downs I am trying to filter a shared Excel document in Microsoft Teams (Can not use VBA or Macros).

    This document is auto populated by a Form so rows are added and edits are made often. I created a separate worksheet in the workbook that is being auto-populated by the form where edits are made which has 5 data validation dropdowns based off of the variables in 5 different rows.

    Using the tutorial I'm going to add in the comments.

    I was able to filter the document with one of the items I wanted to filter by but I'm not able to use multiple filters.

    This is the formula I came up with for the cells that I want to be filtered:
    =IFERROR(INDEX(ResponseEdits!$D$2:$N$2001, $C2,COLUMNS($N$2:N2)), IFERROR(INDEX(ResponseEdits!$D$2:$N$2001,$E2,COLUMNS($O$2:O2)), IFERROR(INDEX(ResponseEdits!$D$2:$N$2001, $G2,COLUMNS($P$2:P2)), IFERROR(INDEX(ResponseEdits!$D$2:$N$2001, $I2,COLUMNS($Q$2:Q2)), IFERROR(INDEX(ResponseEdits!$D$2:$N$2001, $K2,COLUMNS($R$2:R2)), " ")))))


    Any help with this would be extremely appreciated! Please let me know if you have any questions.

    Thank you!
    Sara

  2. #2
    Registered User
    Join Date
    10-02-2020
    Location
    Woodbury, MN
    MS-Off Ver
    365
    Posts
    10

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    It won't let me post the url which had the tutorial.

    it was on trumpexcel.com

    and was called: extract-data-from-drop-down-list

  3. #3
    Registered User
    Join Date
    10-02-2020
    Location
    Woodbury, MN
    MS-Off Ver
    365
    Posts
    10

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    I also found this tutorial on exceljet.net:


    lessons/filter-with-dynamic-dropdown-list

    But again it's only filtering by 1 variable not 5.

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    Can you download the file and then attach it here? Please read the yellow banner at the top of the page.

  5. #5
    Registered User
    Join Date
    10-02-2020
    Location
    Woodbury, MN
    MS-Off Ver
    365
    Posts
    10

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    Thank you! Here is the spreadsheet. I made up some info that could be used as a filter and added the formulas.

    Please let me know if you have any questions.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    If I understand correctly then the attached file does what you want.
    1. The filter formulas in columns B:F are all similar to: =IF(A2="","",IF(INDEX(ResponseEdits!D$2:D$21,MATCH(A2,ResponseEdits!$C$2:$C$21,0))=$I$3,A2,""))
    2. The formula that consolidates the filtered ref's in column G is: =IFERROR(SMALL($F$2:$F$21,A2),"")
    Other formulas are unchanged from those in the file attached to post #5.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    You are using 365 so I think you should be able to use the new dynamic array formulas.
    Secondly, I would recommend you "ResponseEdits" data be in an Excel Table. In my example I've done that, you can do it without creating an Excel Table as well.

    You should be able to get all results with one formula in one cell - in Cell N2, try this:
    =FILTER(Table1[[CampaignStatus]:[URL:]],($M$3=Table1[CampaignStatus])*($M$5=Table1[BusinessUnit])*($M$7=Table1[Region])*($M$9=Table1[FollowUp])*($M$11=Table1[Team]),"None")

    See attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-02-2020
    Location
    Woodbury, MN
    MS-Off Ver
    365
    Posts
    10

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    Thank you!

    That is almost perfect! I was hoping that if one of the drop downs was empty it would stop filtering by that variable.

    Is that possible?

    Thank you again so much!!!
    Sara

  9. #9
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    Who (which solution) are you responding to? For my solution it will show "None" if there are no matches so I'm not sure what you mean by "stop filtering by that variable".

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    As for the proposal in post #6, change the formula in columns B:F to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-02-2020
    Location
    Woodbury, MN
    MS-Off Ver
    365
    Posts
    10

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    JeteMc That is perfect!!!!!

    Thank you both so much!!! It's working like magic now!!!

    I wasn't certain if Excel could even do this.

    You both are lifesavers!

    Thank you again!!!!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: Dynamically Auto Filtering a Shared Excel Document In Teams With IFERROR and Index

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. [SOLVED] Minimize Excel processing time (Formula IFERROR & INDEX used)
    By Bhavesh780 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-21-2021, 10:51 AM
  2. [SOLVED] Code presents error on shared file in teams
    By Chris1976 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-30-2020, 09:15 PM
  3. Replies: 2
    Last Post: 03-03-2016, 02:40 AM
  4. Excel Formula: IFERROR, INDEX, SMALL, MATCH
    By Jenn12788 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2014, 10:03 PM
  5. comparision between two excel sheet using VBA Code for Index/match/and/iferror formula
    By gaurangaero in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2012, 10:08 AM
  6. [SOLVED] How to protect a shared excel document from deletion
    By SteveA in forum Excel General
    Replies: 2
    Last Post: 05-23-2005, 08:30 PM
  7. [SOLVED] Filtering a protected WS in a shared workbook - Excel 2000
    By Phuong Nguyen in forum Excel General
    Replies: 3
    Last Post: 02-18-2005, 07:06 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