Spreadsheet applications are always enticing me. I know spreadsheets are just like an ocean. It carries hidden gems. You can do lots of things in a Spreadsheet based on your logic. Google sheets has no exception to this. To fully understand the power of Google Sheets, you should go deep in to it. Here is yet another awesome Google Sheets tutorial. This time we can learn how to auto populate information based on drop down selection.
I’m using here three Google Sheet functions and a Google Sheet Command. The functions are IF logical function, Unique and Query Function. What about the command then? It’s the Data Validation. If you are not well versed in using this, do not worry. Just read on.
There is one important thing you should know before going to the tutorial section. You should understand what I meant by saying auto populate information based on drop down selection.
See the below screenshot. It contain the reply. It’s a sample data. You can use similar types of data to populate information as per your choice. I picked this sample as I’ve created it already to answer one query I got online.
From the drop down list you can choose any Team. It can be Team 1, Team 2, Team 3 or Free Agent. When I select any team from the drop-down, the name of the corresponding team players should populate in corresponding columns. I’ve selected Free Agent from the drop down list. So the names of the players populated below the Free Agent column.
If you understand the above concept you can continue to our tutorial to auto populate information based on drop down selection.
Few more things to make sure whether you want the above result or are you looking for something else?
If you just want to display any single value corresponding to your selection, you can skip this tutorial because for that you can use Vlookup or Hlookup functions. That Google Sheet tutorial you can find below.
When you want to made a calculation based on a drop down selection, the best way is to use SUMIF function in Google Sheets. Below you can see an example to this.
Now back to the tutorial.
Steps to Auto Populate Information Based on Drop down Selection
Open a new file in Google sheets. We require three tabs in this newly created file. Name the tabs as follows. They are Master Sheet, Team Members and Team Name.
Similar to read: How to Properly Hide Sheet Tabs in Google Sheets
Inside the Team Members sheet, type the information as below. You can input real names under the title Name of Player to easily grasp the result.
After completing the data entry, do one more thing. You should name the ranges. So that we can make the formula in the later part easy to read?
I already explained the naming ranges in Google Sheets. See that for details. Here I’m just telling you how to name ranges for our this tutorial purpose. See that below.
Go to Data > Named Ranges and set the rules as below.
Now go to the tab named Team Name. and there on the very first cell apply the below formula.
The result thus getting will look like as below. This we can use for drop-down selection. I will explain it.
Now it’s time to auto populate information based on drop down selection. To do so go to the sheet named as Master Sheet.
Type the column headings as above in cell B1, C1, D1 and E1.
Now in cell A2 we want the drop down list. To do that go to Data > Data validation and set the data validation rules as below.
Your drop down list is ready now and let’s move to the final steps.
When you select any team from this drop down you need to populate data range in the corresponding columns. Here data range means name of the players. Don’t get? Read on.
Just copy and paste the following query formulas in Cell B2, C2, D2 and E2 respectively.
=if(A2=”Team 1″,query(TeamPlayers,”select A where B=’Team 1′”),””)
=if(A2=”Team 2″,query(TeamPlayers,”select A where B=’Team 2′”),””)
=if(A2=”Team 3″,query(TeamPlayers,”select A where B=’Team 3′”),””)
=if(A2=”Free Agent”,query(TeamPlayers,”select A where B=’Free Agent'”),””)
Any doubt applying the formula? Refer the below image which will give you a very clear idea.
Seems like you interpreted everything mentioned above correctly. If you have done all the steps above correctly you will get the desired result. If things are not working correctly for you just ask me below in comments to share the finished Spreadsheet. I’ll be happy in sharing it with you.
This way you can auto populate information based on drop down selection in Google Sheets.