Easy Way to Populate Selectlist With Percentage Values

Here is yet another awesome Google Sheets tutorial. This time we can learn how to auto-populate information based on a drop-down selection.

Spreadsheet applications are always enticing to me. I know spreadsheets are just like an ocean. It carries hidden gems.

You can do lots of (data manipulation) things in a Spreadsheet as per your logic. Google Sheets is no exception to this.

To fully understand the power of Google Sheets, you should go deep into it.

Here, in this tutorial, we will use three Google Sheets functions and a menu command to achieve our goal.

They are the IF logical function, Unique, and Query Function.

What about the menu command?

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 that you should know before going into the tutorial section.

You must understand what I meant by saying auto-populate information based on drop-down selection.

See the below first two screenshots.

It contains the sample data (screenshot # 2) and output (screenshot # 1).

You can use similar data to populate information as per your choice.

I've just picked this sample as I created it earlier to answer one of my reader's queries.

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 a team from the drop-down, the corresponding player names should appear in the corresponding column.

I've selected "Free Agent" in the drop-down list. So the names of the corresponding players are 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.

Before starting, I want to make clear a few more things.

Skip this tutorial if you want to display any single value corresponding to your selection. 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 make a calculation based on a drop-down selection, the best way is to use the SUMIF function in Google Sheets. Below you can see an example of 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 –Master Sheet,Team Members, andTeam Name.

Similar to read: How to Properly Hide Sheet Tabs in Google Sheets.

Inside theTeam Members sheet, type the below information.

You can input real names under the title "Name of Player." Mine is just a sample.

After completing the data entry, do one more thing. Name the ranges so that you can make the formula easy to read.

I already explained the naming ranges in Google Sheets. Please see that for details.

Here I'm just walking you through how to name ranges for our example purpose. See that below.

Go to Data > Named Ranges and set the rules as below.

Now go to the tab namedTeam 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. We can use this 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 Master Sheet.

Type the column headings in cells B1, C1, D1, and E1. Please refer to the above image for that.

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 the data range in the corresponding column. Here data range means the name of the players.

Insert the following query formulas in cells B2, C2, D2, and E2.

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'"),"")        

If you have any doubt about applying these formulas, please refer to the below image.

I hope you have interpreted everything mentioned above correctly.

If you have done all the steps correctly, you will get the desired result.

If things are not working correctly for you, please feel free to ask me in the comments. I'll be happy to share the sample sheet with you.

In this way, you can auto-populate information based on a drop-down selection in Google Sheets.

Update: Here's the link to the sheet. It's in view mode.

Example Sheet 20719

heatherlyshaus1978.blogspot.com

Source: https://infoinspired.com/google-docs/spreadsheet/auto-populate-information-based-on-drop-down-selection/

Share this post