HomeGoogle DocsSpreadsheet

Auto-Populate Information Based on Drop-down Selection in Google Sheets

Spreadsheet

Published on

Prashanth KV
By Prashanth KV
Modified date:

Another awesome Google Sheets tutorial! This time, we’ll learn how to auto-populate information based on a drop-down selection.

Spreadsheet applications are always enticing to me. I know spreadsheets are like an ocean, carrying hidden gems.

You can do many (data manipulation) things in a spreadsheet, according to your logic. Google Sheets is no exception.

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

In this tutorial, we’ll use three Google Sheets functions and a menu command to achieve our goal.

These are the IF logical function, the UNIQUE function, and the QUERY function. The menu command is Data validation. If you’re not familiar with it, don’t worry. Just keep reading.

There’s one important thing you should know before starting the tutorial.

You must understand what I mean by “auto-populate information based on drop-down selection.”

Here’s a simple explanation:

Auto-populate information based on drop-down selection means that when you select a value from a drop-down menu, other cells in the spreadsheet are automatically populated with information related to that value.

For example, let’s say you have a drop-down menu with a list of products. When you select a product from the drop-down menu, the price and other information about that product are automatically populated into other cells.

This is a very useful feature, as it can save you a lot of time and effort.

Example to Auto-Populate Information Based on Drop-down Menu in Google Sheets

See the first two screenshots below.

The first screenshot (Figure 1) shows the sample data, and the second screenshot (Figure 2) shows the output.

Sample Data:

Sample data for auto-populating information in Google Sheets
Figure 1

Output:

Auto-populating information based on drop-down selection in Google Sheets
Figure 2

You can use similar data to populate information according to your choice.

I chose this sample because I created it earlier to answer a reader’s query.

From the drop-down list, you can choose any team, such as “Team 1,” “Team 2,” “Team 3,” or “Free Agent.”

When you select a team from the drop-down, the corresponding player names will appear in the corresponding column.

I have 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 on how to auto-populate information based on drop-down selection.

Before starting, I want to clarify a few more things.

Skip this tutorial if you want to display a single value corresponding to your selection. For that, you can use the VLOOKUP, HLOOKUP, or XLOOKUP functions.

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.

Now back to the tutorial.

Steps to Auto-Populate Information Based on Drop-down Selection

Open a new Google Sheets file.

We need three tabs in this new file. Name the tabs as follows: Master Sheet, Team Members, and Team Name.

In the Team Members sheet, type the information shown in Figure 1 above (You can also use my sample Sheet shared at the end of this post.)

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

After completing the data entry, name the ranges so that you can make the formulas easy to read.

I already explained how to name 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 the steps below:

  • Go to Data > Named Ranges and set the rules as shown below:
Naming ranges to auto-populate information in Google Sheets
Figure 3

Now, go to the tab named Team Name and apply the following formula to the very first cell:

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

The result will look like the following. We can use this for drop-down selection.

Use UNIQUE function to auto-populate information in Google Sheets
Figure 4

Now it’s time to auto-populate information based on drop-down selection.

Final step to auto-populate information in Google Sheets based on drop-down selection
Figure 5
  • 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 shown below:
Drop-down menu with team selection
Figure 6

Your drop-down list is now ready.

Now, let’s move to the final steps.

When you select any team from this drop-down list, 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:

Query Formulas to Automatically Populate Data Based on Drop-down Menu Item

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

These formulas will populate the corresponding columns with the names of the players for the selected team.

Auto-populated sheet in Google Sheets
Figure 7

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.

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

Example Sheet 20719

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
Sheets Vs Excel Formula

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...
Excel Formula

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...
Excel Formula

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...
Excel Formula

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...
Spreadsheet

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...
Spreadsheet

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

107 COMMENTS

  1. Hi Prashanth,

    I’m trying to use your methods to create a sheet that cross-references the value of the drop-down menu to determine which data to display.

    I want it to check the “records” sheet for a value equal to that of the drop-down value and populate several records at a time.

    You can find a link to my spreadsheet, and hopefully, you’ll understand what I’m trying to achieve. I might not explain myself well in words!

    • You can use the following formula:

      =IFNA(QUERY(QUOTES!A:H, "SELECT * where A='"&B4&"' ", 1))

      This formula is designed to query data from the QUOTES sheet based on the value in cell B4. If there’s anything else you need assistance with, feel free to ask.

  2. Hi Prashanth,

    I am attempting to create a spreadsheet with a drop-down menu for products. My goal is to have the Price column automatically populate when an item is selected. Despite trying various formulas for days, I haven’t had any success. Please provide assistance!

  3. Hi Prashanth,

    I hope you are still answering questions. I am trying to create a spreadsheet that, when you enter a unit number, it will auto-populate the resident’s name, income, and demographics (13 columns of information).

    What I would like is for when we select the unit number for a resident, for example, #101 in column B, it will automatically populate cells C2 through M2, and so on, with their already collected information so that we don’t have to enter it every time for the same person.

    Can you help?

  4. Hey Prashanth!

    Sorry, I couldn’t reply to my other comment as it’s still pending approval.

    I’ve managed to sort out what I was trying to do. All I needed to do was use the basic filter function!! I was overcomplicating it all!

  5. Hi,

    Thank you very much for putting this together. It’s really useful!

    I’m trying to recreate it, but make things a bit more conditional and linked, if that makes sense.

    I’ve amended your formula to what I want, but I know it can’t work. I’m unsure how to get it to work.

    Essentially, I’m trying to create something that would be able to do the following:

    =if(A2=B1:E1,query(TeamPlayers,"select A2:A15 where B2:B15='A2'"),"")

    Which is sort of exactly the same as you have set it up, but using the data in the cells to output the information rather than typing Team 3, etc.

    • Hi Matt Bee,

      I believe I understand your question correctly.

      Delete everything under B2:E and insert the following formula in cell B2:

      =MAP(B1:E1,LAMBDA(col, FILTER('Team Members'!A2:A,'Team Members'!B2:B=col)))

      The formula will return a list of all the team members in the Team Members sheet who are members of the team specified in cells B1:E1.

      The formula does not require a drop-down list.

      Note: I have added a new hidden sheet to the shared file. You can unhide it using the “All sheets” button (three horizontal bars on the bottom left).

      I hope this helps.

  6. Hi,

    I’m not sure if you’re still responding, but thank you for the templates.

    I have a question. I’m creating a meal plan to track protein, carbs, fats, and calories for a day and a week. I plan to use your template and change some of the information.

    I’m using the following logic statement:

    =if(A2="Team 1",query(TeamPlayers,"select A where B='Team 1'"),"")

    The information is input vertically, but I’d like it to be displayed horizontally when an item is selected. I’d like to have a drop-down menu that pulls the protein, fats, carbs, and calories into the same horizontal line as the drop-down menu.

    What would I need to change?

    • Hi Brandon,

      You need to wrap the results with TRANSPOSE or TOROW to output the results in a horizontal row.

      For example, the following formula will output the names of the players on Team 1 in a horizontal row:

      =TOROW(if(A2="Team 1",query(TeamPlayers,"select A where B='Team 1'"),""))

      If you share your spreadsheet with sample data filled in, I would be able to give you a correct solution.

  7. Hi,

    I’m trying to create a Google Sheet where I can list all of my tasks on one tab, choose a category for the task from a dropdown, and have that task name go to the corresponding tab. I thought I had it figured out with XLOOKUP, but it is repeating each task name several times. Any suggestions?

  8. Hey Prashanth!

    I tried the Name Range, exactly like you did, but it doesn’t give me the result:

    [URL removed by admin]

    Also, do I basically need to make an IF formula statement for each country in my example? “Australia”, “Sweden”, etc.?

    Thanks!

  9. Hi,

    I would like to share my sheets with you. I have one master sheet and one target sheet. The master sheet contains customer names, discounts, and remarks. The target sheet has a drop-down list for the name, but when I select a name, it does not show any discounts or remarks.

    Would you be able to help me troubleshoot this issue?

  10. Hi, I have to select a name from a drop-down list.

    It should show the value associated with that name. If I select “Padma,” it should show his age and email address. Any help on this? How to do that.

  11. Hi Prashanth,

    I’m trying to make a Sheet where a movement is selected; the EF value auto-populates the score 1-4.

    If nothing is selected, I need the value to equal 0. Any help would be appreciated.

    • Hi, Carissa,

      Thanks for your Sheet and edit permissions.

      You require to add IFNA to return 0 when VLOOKUP returns N/A.

      Syntax: ifna(vlookup_formula,0)

      I’ve added that and further modified your formula to an array formula that spills down.

  12. Hi my friend

    Is this formula wrong?

    =ifs(A2="Mar 1",query(Birds,"select A where B='Mar 1'"),
    A2="Mar 2",query(Birds,"select A where B='Mar 2'"),"nil")

    I aim to create a birding catalog. And I will constantly add conditions and values for each birding trip I complete.

    In this formula, nothing shows up.

    What should I do?

    • Hi, Ed,

      Thanks for your Sheet with editable rights.

      I’ve added the below solution in cell E2.

      =query('birds data sheet'!A:B,"Select A where B='"&A2&"'")

  13. Hi Prashanth,

    Is it possible to have a cell auto-populate data with vlookup (or another formula) dependent on what is selected from 3 different dropdown lists? I’m building a recommendations table and I want the user to be able to select A in one dropdown, B in another, and C in the third and then the column next pulls out the related info in the associated table in the same sheet.

  14. Hi, my specific question is about pulling a name from a dropdown in column G to paste a number to column H.

    Such as, if the dropdown name in cell G=Secho, then it would produce the number 93351 to appear in the H cell next to it.

    I tried several tutorials, and I either cannot get it to paste, or it creates an error. Thank you very much for having this forum.

  15. Hi Prashanth,

    I am trying to create a meal plan template where I select a dish (from a drop-down menu), and then it populates the list of ingredients I would need to make the said dish. I am having trouble translating your example into my page. Anyway you could help? Here is a link to view a sample of the template.

    — URL Removed —

  17. Hello,

    I have a google sheet with two tabs (Master Sheet and Collateral Sheet).

    I have dropdown lists for the first three columns (A, B, C) on the Collateral sheet.

    The dropdown list options come from the three columns (A, B, C) on the Master Sheet.

    Is there a way to pull a row of information (A-J and then U-AG) from the Master Sheet to the Collateral Sheet if you select from the dropdown list options?

    Ex:
    Say the Master Sheet On Row 1 states;

    Column A | Column B | Column C
    Chance | Property | Collateral

    If I go to the Collateral Sheet Tab and select “Chance” under Column A, “Property under Column B, and “Collateral” under column C, what code would allow the Collateral sheet to pull the rest of the columns on for that row in the Master Sheet?

    Does that make sense?

    • Hi, Chance,

      I assume the drop-downs are in A1, B1, and C1 in the “Collateral Sheet.”

      If so, try this formula.

      =filter({'Master Sheet'!A1:J,'Master Sheet'!U1:AG},'Master Sheet'!A1:A=A1,'Master Sheet'!B1:B=B1,'Master Sheet'!C1:C=C1)

  18. Hello, I’m trying to use the drop-down function where I can select different options in multiple cells (Fan Settings), and it calculates an air quality metric that uses the sum of values associated with the combinations of what was selected.

    Do you think you could help me with this? Thanks!

  19. Wow! I figured it out!

    Now I have a “NA” value if nothing is selected, but I need to figure out how to make it have a value of “0” rather than NA (so that I can do calculations on the table)

      • Thank you very much for your help! I am going to need to study the Array formula on your page.

        Is this the superior method rather than having multiple rows with repeated VLOOKUP formulas?

        It certainly is cleaner for the lack of a better way of putting it.

        I suppose my question would be how would you create the sheet, and the answer most likely using an array formula.

        Thank you again! I am excited to learn more!

  20. Hi,

    Thank you for this useful article.

    I tried your formula but got an error. Can you please look into it?

    Here is the URL: — removed by admin —

  21. Instead of team1, team 2, etc., I have a list of names that are in a drop-down.

    I want to display the email address associated with whichever name is selected from the drop-down. So, the formula would be something like:

    =IF(A2="DROPDOWN_NAME_FROM_ROW",QUERY(NAMES_LIST_TAB,
    "SELECT A WHERE B='DATA_FROM_EMAIL_CELL_IN_ROW'"),"")

    Would this work? How can I get the IF function to work from a variable based on the Name drop-down?

    • Hi, Sara,

      It seems you require to use a Vlookup as below.

      =vlookup(A2,{names_list!B1:B,names_list!A1:A},2,0)

      Where;

      A2 – the drop-down value.
      names_list – tab name containing the list of names and email IDs.
      B1:B – the column contains the list of names.
      A1:A – the column contains email IDs.

  23. Hi Prashanth,

    I am creating a daily schedule that changes each day.

    I want to be able to select under “Weekday” the day of the week I need to see, and then once the day is selected, the columns “Time,” “Subject,” and “Notes” populate the schedule to correspond with the day selected.

    Is this possible? Here is the link to my document:

    — Sheets’ URL removed —

    Thank you for any help you may offer!

    Dori

  24. Hi Prasanth,

    Thank you so much for this cool tutorial! I tried following your instructions, but my data isn’t populating, which I think is because I am using a horizontal format.

    Could you take a look and let me know if it’s still possible to use this method?

    —link/URL removed —

    Thanks!

  25. Hi Prasanth,

    Could you please assist me with this function?

    I want a column (Probability see below) to automatically populate a percentage (predetermined) based on a selection made in a drop-down list in another column (Stage, see below).

    For each of the Stage options, I have a preset % I want to be used, and that is the amount I want to auto-populate.

    Percentages against each of the stages are:

    Probability
    0%
    0%
    10%
    30%

    Stage

    1. Identify opportunity (using the list above, this would be 0%)
    2. Clone opportunity (using the list above, this would be 0%)
    3. Present (using the list above, this would be 10%)
    4. Send RFP (using the list above, this would be 30% and so on)

    • Hi, Peta,

      That seems possible.

      Steps:

      1. Enter the “Stage” values in B2:B and the corresponding “Probability” percentage values in C2:C.
      2. Create the drop-down in cell E2 that contains all the “stage” values.
      3. Insert the following formula in F2.

      =to_percent(ifna(Vlookup(D2,B2:C,2,0)))

  26. Hi Prashanth!

    It has been a game-changer! I was able to follow your tutorial to consolidate the columns into one.

    I’m trying to label product status for each of my products across all stages of development (column C).

    Each stage has different deliverables that I want to populate in a single column (column E).

    — Sheet’s URL removed by Admin —

  27. Hi Prashanth

    I want to create a drop-down selection box at the top of the sheet. Once the user selected the Category item from the drop-down, only the rows with the corresponding category item will be shown

    —Link (Sample Sheet) removed by the admin—

    How can it be done?

    Thanks in advance!

    • Hi, Margaret,

      You can either use the SLICER or the below Filter formula.

      =filter(Sheet1!A4:G,Sheet1!E4:E=B1)

      Your column E contains the category. So created a drop-down in cell B1 to select categories from this column.
      The Filter formula in cell A4 filters column E, i.e. the category, based on the drop-down selection.

      The above solution provided in a copied sheet “kvp ii” in your spreadsheet. For the SLICER, please see “Sheet1”.

  28. I created a drop-down on “sheet 1” with list values Driver, monitor, part-time staff.

    No problem with that using Data Validation.

    On “sheet 2” I have values for this list of values, for example, If they select Driver from the drop-down, Driver needs to be replaced with the code 830 which will be on “sheet 2”.

    That code needs to populate in the same cell.

    Hope you will respond.

  30. Hey, this is marvelous!

    Is there a way to have multiple options within the same 2 cells? For example: If A1 says “Bedroom”, B1 will say “200”, but if A1 says Bonus, B1 will say 500, etc.

  31. I want essentially create a button to navigate the sheet. The user would select a value from the dropdown, this would trigger a function that finds the corresponding data in an adjacent column on the Master Navigation tab and populate that value with the hyperlink.

    The client could then click the link to jump down to the section of the sheet they want to see. I got everything to work with this method except the hyperlink won’t carry over. Any tips?

  32. Required Row 10 Vlookup with values and droplist intact. No Formula, so that I can edit & updates to master range A13:N18 dynamic row.

  33. Good Day Prashanth,

    Hope You can assist in this request.

    1: Master sheet Vlookup at A7.
    2: Row 9 normal Vlookup with formula. DONE!
    3: Row 10 Vlookup result, REQUIRE to shows only values and with dropdown available. Need your help? Any method is fine as long Row 10 values and droplist available is the same copy as Row 9.
    4: Row 13 – Section A to D columns “To show Only section columns” base upon any “C13:C18” drop list value selected and hide all other sections.
    5: A1:G5 is your formula.
    Question?
    6: Required ROW 9 Vlookup with values and droplist intact. No Formula so that I can edit & updates to master range A13:N18 dynamic row.
    7: M19 – Are some of the possible solutions I can think of. I prefer Method 1 solution if possible.
    8: If you can email me, I can send you a video explanation in case my explanation is not clear.

    Hope you can edit the formula in the Master sheets.
    … link removed by admin …
    Thank you so much in advance!!

    • Hi, Jason,

      I have replaced the VLOOKUPs in cell A9:N9 with the following array formula in cell A9.

      =ArrayFormula(vlookup(A7,A13:N18,sequence(1,14),0))

      And in cell B10 I have used a simple formula =B9 and which copied to its right.

      Note: I don’t know the purpose of copying the Vlookup result from row#9 to row#10. If you manually select any value in the drop-down in row#10, the formula in that cell won’t work anymore. Your requirement may meet by Google Apps Script. You can search “https://stackoverflow.com/” or ask in that community for assistance.

  34. Hi Prashanth,

    I was wondering if you were able to assist me. I am trying to find the correct formula to use to auto-populate a cell that based on 2 drop-down menu selections.

    I.e. selecting a product name from drop-down menu #1, selecting the store it was purchased from in drop-down menu #2 to then give the cost price for what was selected in column 3.

    I understand I need to have the data in a different tab but I’m not sure which formula to use from there… Vlookup, If??

    Thank you in advance.

    • Hi, Lisa Juniper,

      I think I can definitely help you!

      There are two-three solutions (Query, Filter, or Vlookup). The better one would be a Vlookup.

      Share your sheet via comment (either in View or Edit, the comment won’t be published). Please remove personal info., if any, before sharing.

  35. I need to do this same thing with an employee roster based on the department.

    So department would be team # and employees would be player plus I would need to pull in additional 5 columns of data (employee name, department, phone number, email, qualifications).

    I can’t get the formula to adjust to pull in the additional columns based on A2 being the department.

    • Hi, Jay,

      We can simply use a Filter or Query formula.

      Assume the said table is in the “employee data” sheet (tab name) and the column order is employee name, department, phone number, email, and qualifications in A1:E. Obviously the first row will contain the labels.

      In a blank sheet in that file create the drop-down in cell A2 and in cell B2, either you may use the Query;

      =query('Team Members'!A1:E,"select * where B='"&A2&"'",1)

      or Filter;

      ={'Team Members'!A1:E1;filter('Team Members'!A2:E,'Team Members'!B2:B=A2)}

      to auto-populate the data based on the criterion in cell A2.

      Please note the regional (File > Spreadsheet settings > Locale) settings may affect the formula (comma to semicolon issue). Read more about that here – How to Change a Non-Regional Google Sheets Formula.

  36. Hi,

    Can I combine the formulas so dependant on the drop down the text will populate in one column of cells instead of using 4 different columns?

    Thanks.

    • Hi, Natasha Laidlaw,

      Did you copy my sample Sheet? If so, in cell G1 in the ‘Master Sheet’ sheet you can see the formula for the same.

      Here is that formula for your quick use.

      ={A2;query(TeamPlayers,"select A where B='"&A2&"'",0)}

  38. Thanks for this and this is really helpful.

    What I’m trying to do is when the drop-down says “Team 1”, it displays in Column B, and when “Team 2” is selected it will replace what’s there and then show “Team 2”, and so on and so forth.

    So instead of having a column for every team, I want them all in the same column but as to replace each other based on the Dropdown list. Hope this makes sense!

  40. Right, that might work, but my actual sheet got unexpected_char error. Seems I can not use this if my cells contain symbols? And If I want to expand it to 4 columns per selection?

    • Hi,

      You are not allowed to use a mixed type of data in Query like text, numbers, date, symbols, etc in a single column.

      To expand to four columns, change the +1 in the last part of the formula to +3.

      If you can share a demo Sheet, I can possibly find the cause of the error.

  41. Hi.

    I want to make a dropdown menu to show different ranges of information. Like this:

    Menu to show information from different selected ranges

    So, when I change the dropdown, I get the info in the 2 columns under there.

    • Hi,

      As per your example, the data range is D3:Q and the range D1:Q1 contains the labels for the drop-down.

      So you can use the below Query in cell A3.

      =Query({D3:Q},"Select Col"&match(A1,D1:P1)&",Col"&match(A1,D1:P1)+1)

      This formula will return two full columns based on the drop-down menu selection.

      Best,

  42. Hi Prashanth,

    I am trying to make a spreadsheet where I have one tab with my ‘master data’ and one tab where, if I select an option from a drop-down menu, it populates categories with information from the master data. I have attached it here:

    “Link Removed by the Admin”

    I am able to make the drop-down menu, but I don’t know how to populate the value categories with information from the ‘master data’ tab.

    I know there are several ways to retrieve this data but none of them seem to be working. I also want the retrieval to work even as I add new information to the master data tab.

    • Hi, Bella,

      For that, you can use a Vlookup formula.

      =ArrayFormula(transpose(vlookup(A3,'Master Database'!A1:M,{1,2,3},0)))

      This formula will return values from your master database column 1, 2 and 3 based on the drop-down selection.

      Change this column numbers to retrieve information from other columns.

      I have made a copy of your sheet. Find the formulas there.

      Demo Sheet Link

      Best,

  43. Hi Prashanth,

    I’m trying to do something similar, but not quite the same as what you’ve done, and unfortunately, I’m just not experienced in this.

    I’d like to make a selection in a dropdown menu on one sheet, and I want to have the entire row move automatically to another tab in the sheet based on my selection.

    For instance, if my dropdown selections are Blue, Yellow, Green – when I select Blue from the dropdown menu in one cell, I want that entire row to move to the tab labeled Blue. Is there a way to do this? Are you able to walk me through it?

    • Hi, Les,

      I think we can do this. Here is one example (If I understand your question correctly)

      I have the drop-down menus in Cell A1, A2, A3 … In that, I have selected “Blue” in cell A1 and A3.

      We can use this Filter formula in the “Blue” tab.

      =filter(Sheet1!A1:Z,Sheet1!A1:A="Blue")

      See if that helps. If not you can make an example sheet and drop the link in your reply below.

      Best,

  45. That was brilliant. I was wondering if we could reverse the function where I populate a list of words that act as keywords, and when the keywords are typed in column B, column A will automatically be set to a Category which contain the keywords in column B.

    • Hi Luke,

      I’m sorry that I couldn’t interpret your question correctly. But I’ve updated the post with the link to my sheet. You can try your luck with that sheet.

    • Hi Luke,

      On second thought I understood your question. Following the above steps in my tutorial, it’s not possible. But as a recommended method, on a second sheet, you can easily remove the duplicates. As you may know, you can use the UNIQUE function to remove duplicates. You can have further control over the duplicate removal if you use SORTN. If you are new to Google Sheets, you can check my functions guide on the top navigation bar.

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.