How To Auto Populate Dates In Excel

Spreadsheet applications are ever enticing me. I know spreadsheets are just similar an ocean. It carries subconscious gems. Y'all can do lots of things in a Spreadsheet-based on your logic. Google Sheets has no exception to this. To fully empathise the power of Google Sheets, you should become deep into it. Here is however another awesome Google Sheets tutorial. This time nosotros can learn how to motorcar-populate data based on drib-down selection.

I'thousand using here three Google Canvas functions and a Google Sail Command. The functions are IF logical function, Unique and Query Function. What near the command then? It'southward the Data Validation. If you are not well versed in using this, do non worry. Just read on.

There is ane important matter you should know before going to the tutorial section. You should understand what I meant by saying auto-populate information based on driblet-down selection.

Example to Machine-populate Data Based on Drib-down Bill of fare in Google Sheets

See the below screenshot. Information technology contains the reply. It's a sample data. You tin utilize similar types of data to populate information as per your choice. I've just picked this sample equally I created information technology earlier to reply i user's query that I got online.

From the drop-down listing, you tin choose any Team. It can be Team 1, Team ii, Squad iii or Free Agent. When I select any squad from the drop-downwards, the name of the corresponding squad players should populate in corresponding columns. I've selected Free Amanuensis from the drop-down list. So the names of the players populated below the Free Amanuensis column.

Auto populate information based on drop down selection in Google Sheets

If you sympathise the higher up concept you tin can continue to our tutorial to automobile-populate information based on drop-down selection.

Before starting, I want to brand articulate few more than things. If y'all just want to display whatsoever single value corresponding to your selection, y'all can skip this tutorial because for that you can use Vlookup or Hlookup functions. That Google Sheet tutorial you lot can find below.

How to Employ Vlook and Hlookup in Google Sheets

When you want to brand a calculation based on a drop-down choice, the best way is to use SUMIF function in Google Sheets. Beneath you can see an example to this.

Create a Unit of measurement Calculator Using Google Sheets

Now back to the tutorial.

Steps to Motorcar Populate Data Based on Drop-down Selection

Open a new file in Google Sheets. Nosotros crave three tabs in this newly created file. Name the tabs as follows. They are Master Sheet,Team Members, andSquad Proper name.

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

Within the Squad Members canvas, blazon the data as below. You tin can input real names under the title Name of Actor to easily grasp the result.

Sample data 1 for auto populate info in google sheets

Afterward completing the data entry, practise ane more thing. You should name the ranges. So that nosotros tin make the formula in the later part easy to read?

I already explained the naming ranges in Google Sheets. Meet that for details. Here I'thousand just telling you how to proper name ranges for our this tutorial purpose. Meet that below.

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

Naming ranges to auto populate info in Google Sheets

Now go to the tab namedSquad Proper name and there on the very commencement cell apply the below formula.

          =UNIQUE('Team MEMBERS'!B2:B30)

The result thus getting volition look similar as below. This we tin can employ for drop-downwardly selection. I will explain it.

Use unique function to auto populate info in Google Sheets

At present it's time to car-populate information based on driblet-down pick. To do and so go to the sheet named asMaster Canvass.

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

Type the column headings equally higher up in cell B1, C1, D1, and E1.

Now in cell A2, we want the drop-down list. To do that get to Data > Data validation and set the information validation rules as beneath.

Set data validation to auto populate info in Google Sheets

Your drib-down list is ready now and let'south move to the last steps.

When you select any squad from this drib-down you need to populate data range in the corresponding columns. Here data range means the name of the players.

Just copy and paste the post-obit query formulas in Cell B2, C2, D2 and E2 respectively.

Query Formulas to Automatically Populate Data Based on Drop-Downwards Carte Item

Offset Formula.

          =IF(A2="TEAM 1",QUERY(TEAMPLAYERS,"SELECT A WHERE B='Team 1'"),"")

2d Formula.

          =IF(A2="TEAM ii",QUERY(TEAMPLAYERS,"SELECT A WHERE B='Squad 2'"),"")

Third Formula.

          =IF(A2="TEAM 3",QUERY(TEAMPLAYERS,"SELECT A WHERE B='Squad 3'"),"")

Fourth Formula.

          =IF(A2="FREE Amanuensis",QUERY(TEAMPLAYERS,"SELECT A WHERE B='FREE AGENT'"),"")

If you have whatsoever doubt applying these formulas, please refer the beneath epitome.

Finished auto populated sheet in Google Sheets

Seems like you lot interpreted everything mentioned above correctly. If you have done all the steps higher up correctly y'all volition get the desired result. If things are non working correctly for you merely ask me below in comments to share the finished Spreadsheet. I'll be happy sharing it with you.

This way you tin motorcar-populate information based on a drop-down option in Google Sheets.

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

Example Sheet 20719

Delight make a copy of this file from the file menu and that will be editable.