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:
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:
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
.
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)
Bookmarks