+ Reply to Thread
Results 1 to 14 of 14

How can I add an 'All' option to my dropdown list filters?

  1. #1
    Registered User
    Join Date
    02-01-2023
    Location
    Crediton, England
    MS-Off Ver
    365
    Posts
    7

    How can I add an 'All' option to my dropdown list filters?

    Doing a Excel template up for my local swimming club, whereby they can enter in the competitors data into a sheet (Called Data Input) including the following:
    • Name
    • Gender
    • Age
    • Stroke
    • Distance
    • Time

    I've then selected these 6 columns and named them as SwimData.
    In a separate sheet (Called Data Lists) I then create dynamic lists with the following formula:
    Please Login or Register  to view this content.
    Which then grabs all the data from the Data Input worksheet and that particular column and sorts it into alphabetical order and will update any new results e.g. a new name etc.

    In a final worksheet (Called Filtering) I have a table with the original 6 headers (Name, Gender, Age, Stroke, Distance & Time) and on the cell below the "Name" header I use the following formula:
    Please Login or Register  to view this content.
    To the right hand side of this table I have 5 dropdown lists with data validation which each use a dynamic list from the Data List sheet, for example the dropdown list for Names uses the following source
    Please Login or Register  to view this content.
    .

    What I want to be able to do is:
    • Add an "All" option to my dynamic list's dropdown list
    • Display all data when the "All" option is selected on the relevant filter.

    For example by default all of the filters will have the "All" option selected and all the data will be visible like it is shown in the Data Input worksheet; however when you then select an option from a particular filter it will then display the data specific to that filter but all the data for the remaining filters.

    For example If I wanted to see all Male Swimmers that competed in the 100 Metres Backstroke then I would set the filters as such:
    • Name = All
    • Gender = Male
    • Age = All
    • Stroke = Backstroke
    • Distance = 100 Metres

    And then it would display everyone that was a male swimmer that competed in the 100 Metres backstroke, now if I suddenly decided to change the distance filter to 200 Metres then it will show me only the Male swimmers that took part in the 200 Metres Backstroke.

    Workbook attached (If I did it right, 1st timer here)
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: How can I add an 'All' option to my dropdown list filters?

    For the Lists you can use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and the filter formula would be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-01-2023
    Location
    Crediton, England
    MS-Off Ver
    365
    Posts
    7

    Re: How can I add an 'All' option to my dropdown list filters?

    Thank you that worked exactly as I want it to do ,except when putting all of the filters as "All" it will show a #VALUE! error
    Just a separate question, the swimming club has set age brackets for groupings.
    This is only apparent for ages 8 and below & Open (17 and above).

    How would I go about editing the dropdown list for the Age Filter so that it displays:
    • 8 & Under - Displays swimmers with ages below the age 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • Open - Displays all ages 17 & above

    And also setting it so that it displays only ages for that age bracket within the filter formula?
    So that when the 8 & under age bracket is selected, it will only display swimmers with an age of 8, 7 ,6 etc. & vice versa for the Open age bracket it would display data with a swimmers age being either 17,18,19,20 etc?
    Last edited by Dragonfly3r; 02-02-2023 at 09:01 AM.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: How can I add an 'All' option to my dropdown list filters?

    Just set the list manually & then use
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-01-2023
    Location
    Crediton, England
    MS-Off Ver
    365
    Posts
    7

    Re: How can I add an 'All' option to my dropdown list filters?

    Quote Originally Posted by Fluff13 View Post
    Just set the list manually & then use
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    So I've removed the VStack formula for the Age group and manually entered into the list the following:
    • 8 & Under
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • Open

    And on the Filtering sheet, if I have the filters as:
    • Name = All
    • Gender = Male
    • Age Group = All
    • Stoke = All
    • Distance = All

    It will display all the ages for the male swimmer, if I switch the Age Group filter to 8- or Open it will display only the ages set for that section; however if I set the Age group to any of the other number (9,10,11,12,13,14,15,16)
    It will display those results but then it will display the previous selection results.

    Also if I set all the Filters to show "All" it will still show that #Value! error.

    Tried to attach gifs showing the issues but can't so have reattached the workbook for you to look over the issues.
    Attached Files Attached Files
    Last edited by Dragonfly3r; 02-02-2023 at 09:53 AM.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: How can I add an 'All' option to my dropdown list filters?

    A stray< crept in, it should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    02-01-2023
    Location
    Crediton, England
    MS-Off Ver
    365
    Posts
    7

    Re: How can I add an 'All' option to my dropdown list filters?

    Quote Originally Posted by Fluff13 View Post
    A stray< crept in, it should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Issues above - Still persist after removal of the stray <

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: How can I add an 'All' option to my dropdown list filters?

    In that case can you supply a workbook that shows the problem.

  9. #9
    Registered User
    Join Date
    02-01-2023
    Location
    Crediton, England
    MS-Off Ver
    365
    Posts
    7

    Re: How can I add an 'All' option to my dropdown list filters?

    Quote Originally Posted by Fluff13 View Post
    In that case can you supply a workbook that shows the problem.
    As requested, workbook has been attached.

    Steps to reproduce #VALUE! Error issue
    1. Open up Filtering worksheet
    2. Change any filter from All to desired selection
    3. Now change that same filter back to the selection "All"
    4. Make sure all other filters are set as "All"
    5. Result Produced - Cell states #VALUE! Error

    Steps to reproduce age group duplicating issue:
    1. Open up Filtering worksheet
    2. Set all Filters to "All" selection
    3. Change Age Group Filter to 8-
    4. Now change Age Group Filter to 9
    5. Optional - Change Age group filter to 12 or 14
    6. Result Produced - Previous data for 8-, 9 & 10 are also display instead of just the specified age group
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: How can I add an 'All' option to my dropdown list filters?

    For the age group you removed the wrong < it should be <=8 and =K3, to get rid of the #value use
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    02-01-2023
    Location
    Crediton, England
    MS-Off Ver
    365
    Posts
    7

    Re: How can I add an 'All' option to my dropdown list filters?

    Perfect that fixed everything.

    Just on a non-formula based question.
    When I enter times into the Data Input worksheet.

    If say the time was 01:12:33 which would stand for 1 min, 12.33 seconds.
    How could I type that in as 011233 and have it format it 01:12:33 Because currently when I enter in times using that format it will set the data in cell to 00:00:00 but also give it a year e.g. 10/06/1903

    If you look at the previously attached workbook (prior) post and look at the cells under Time in the Data Input worksheet you can see this.

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: How can I add an 'All' option to my dropdown list filters?

    for 1 min 12.33 secs you should enter it as 1:12.33 anything else will not be a time

  13. #13
    Registered User
    Join Date
    02-01-2023
    Location
    Crediton, England
    MS-Off Ver
    365
    Posts
    7

    Re: How can I add an 'All' option to my dropdown list filters?

    Ah I see, that's a pity, okay.

    Just noticing though when I type in as 01:12.33 it will only display it as 00:01:12 and won't show anything past the decimal point for the seconds, how I can get it to show the seconds?
    Edit: Fixed this, just had to change field to custom instead of time

    Regardless though, thank you very much for the help as whilst I can do detailed excel documents, but doing anything formula wise I often end up stumped for even the most simplistic of things.
    Last edited by Dragonfly3r; 02-02-2023 at 10:58 AM.

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,030

    Re: How can I add an 'All' option to my dropdown list filters?

    Glad to help & thanks for the feedback.

+ 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. Assign data to option of dropdown list
    By joosttrommelen in forum Excel General
    Replies: 2
    Last Post: 04-21-2020, 10:03 AM
  2. Cells reset formula from a Dropdown list
    By Khaldon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2018, 10:17 AM
  3. Dropdown box suggested list option by typing the first letter of an option
    By jennis7242 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-19-2017, 10:39 PM
  4. [SOLVED] If an option in dropdown list selected return value 0
    By hanif in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2016, 10:16 AM
  5. using a dropdown list instead of option buttons
    By JJFletcher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-20-2015, 10:16 PM
  6. dropdown list using filters
    By salimnore in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-28-2014, 10:51 PM
  7. Change multiple pivot table filters with dropdown list in cell
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2013, 09:19 AM

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