Drop down list to select rows - must also include "ALL"

[EdPivco]

In my spreadsheet I have a row named "Month". I want to a drop drop list containing all of the months and, in addition, having the word "ALL". The user can either select a particular month and see every row that has that month or can choose the word "ALL" and see all of the rows. How would I do this?

Sample Spreadsheet:

Name Month
John January
Bob February
Mary March
Joe March

If the user selects March, he will see the last two rows. If the user chooses "ALL", he will see all four rows.

Thank you.
Ed

[6StringJazzer]

The easiest way to do this is to select the columns, then use AutoFilter (Data > Filter). This will automatically create a dropdown for each column in the first row which will allow you pick any month, or All.

autofilter.jpg

[EdPivco]

I appreciate the reply, but I didn't state my question clearly. I've attached a spreadsheet. If you click on the drop down list in cell H1, it give you the two options "Smith" and "Jones".
I want three options, "Smith", "Jones", and "ALL". If the user clicks on "All", then all rows of the spreadsheet will be shown.

[AliGW]

On Sheet2 in A1:

=VSTACK("All",UNIQUE(Scores[Teacher]))

On Sheet1 in G4:

=IF(H1="All",Scores,FILTER(Scores,Scores[Teacher]=H1))

[EdPivco]

Thank you for the response. Is there a way to adjust this so that the drop down list is displayed alphabetically?

[EdPivco]

This spreadsheet works perfectly now (except for the alphabetized drop down list - which I can live without).
But, if I open with Microsoft Office Profession Plus 2016, it only works correctly if I choose the "ALL" option in the drop down list.
If I choose anything other than "ALL", I get: #NAME? for every entry in every row.
I created this spreadsheet using Microsoft 365 for Enterprise (and it works perfectly there).

Any ideas?

[6StringJazzer]

VSTACK, FILTER, and UNIQUE are available in 365 and were all implemented after 2016. I'm assuming this solution was offered based on your profile, since you did not mention you would need it to work on 2016.

I believe a solution may still be possible but it's going to be more complex.

[6StringJazzer]

Here is a solution that is backwards compatible to older versions, but it requires a helper column added to the table. You can hide this column if desired. It may be possible to develop a solution that does not require the helper column.

It uses an array formula to get the distinct teacher names in the same place that Ali put it.
It uses a named range TeacherList for these distinct names rather than a direct cell reference. This name is used in the Data Validation list formula.
It uses three formulas to retrieve the corresponding rows from the table, one for each column of data.

[EdPivco]

Thank you for the solution. Would you be able to explain to me exactly how you did this?