Auto Populate Information Based on Drop down Selection in Google Sheets

3
474
Populate info in Spreadsheet

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. 

Populate info in Spreadsheet

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.

auto populate information based on drop down selection in Google Sheets

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.

How to Use Vlook and Hlookup in Google Sheets

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.

Create a Unit Calculator Using Google Sheets

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 SheetTeam 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.

sample data 1 for auto populate info in google sheets

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.

naming ranges to auto populate info in Google Sheets

Now go to the tab named Team Name. and there on the very first cell apply the below formula.

=unique(‘Team Members’!B2:B30)

The result thus getting will look like as below. This we can use for drop-down selection. I will explain it.

use unique function to auto populate info in Google Sheets

Now it’s time to auto populate information based on drop down selection. To do so go to the sheet named as Master Sheet.

final step to auto populate info in Google Sheets based on drop down selection

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.

set data validation to auto populate info in Google Sheets

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.

First Formula.

=if(A2=”Team 1″,query(TeamPlayers,”select A where B=’Team 1′”),””)

Second Formula.

=if(A2=”Team 2″,query(TeamPlayers,”select A where B=’Team 2′”),””)

Third Formula.

=if(A2=”Team 3″,query(TeamPlayers,”select A where B=’Team 3′”),””)

Fourth Formula.

=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.

finished auto populated sheet in Google Sheets

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.

3 COMMENTS

  1. I can’t get this to work, it throws up errors on Master Sheet in Cell B2. A Parse error. I have cut and pasted it from the website but it is not working. Any ideas as to why? I would be very grateful!

LEAVE A REPLY

Please enter your comment!
Please enter your name here