We will put this drop-down list into cell G4 of the spreadsheet.
To do this: You will now have a drop-down menu in cell G4, that displays the team members relating to the team name that is currently typed into cell G2 (see below).
We can use this data to create a drop-down list that contains just the staff members for a selected team, and automatically updates when a different team name is selected.
Note that, in the above example spreadsheet, the formula "=$G" in cell E1 reflects team name that is entered in cell G2, and the formulas in cells E2 - E6 use the Hlookup and Row functions to look up the members of this team.
There is a way to do this, but it is not as intuitive as you would think.
In order to accomplish what you want, you can use the same type of table layout you have there, but you'll need to add a bit more too it: If you do not have a Developer tab, or menu at the top, edit your preferences, usually in the advanced section, and check, or enable the Developer option, so you have the menu.
Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate.
As soon as you make the selection from the drop down, you need Excel to automatically identify the records that belong to that selected item. Here are the steps to create helper columns: Now we need to extract the data for these rows only, which displays the number (as it is the row that contains that country).
When you create an App Sheet application from a worksheet having data validation rules, App Sheet will automatically detect these data validation rules and apply them to your App Sheet application.
This ensures that data values entered through your App Sheet application conform to the same rules as data values entered directly into your worksheet.
In the above example, the first step is to get the unique list of all the countries.
Here are the steps to get a unique list: The goal now is to select any country from the drop-down list, and that should give us the list of records for the country.
The following example shows you how to use Excel formulas to create a variable drop-down list.(If you are not familiar with drop-down lists in Excel, you may want to first view the page on How to Create a Basic Drop-Down List in Excel).