CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Bamieschijf
Bamieschijf

Reputation: 51

Create dropdown list from data tab (conditional)

Not sure how to phrase this correctly, but I can't get my head around the next thing; I have a Google Sheet with two tabs. One tab showing the data based on the selected parameters (ID and Category) and one tab with all of the data.

The data consists out of one ID and up to 4 categories with each category having 3 unique values. When selecting an item ID in the first tab, it should generate a dropdown list with all of the Category names of the item with the same ID from the data tab. It should only pull the categories which are not empty. Once an ID and one of the categories is selected in the first tab, it should pull the 3 unique values of that specific Category and display it in the first tab.

Tab 1

The dropdown list in the first cell is simply the range of all the values in the 'B' column of the data tab. Once an ID has been selected in Tab 1, it should automatically generate a dropdown list including all Category Values (if not empty). In the example below it should create a dropdown list with the values "Example 1 and Example 2". If there would be a value below Category 3 it should create a dropdown list with 3.

Once the ID and the Category has been selected, it should pull the corresponding First, Second and Third value from the selected category.

enter image description here

Data tab

enter image description here

Upvotes: 0

Views: 1115

Answers (1)

Oriol Castander
Oriol Castander

Reputation: 648

There are several ways to achieve this behavior, but I would suggest using the =FILTER function (you can read more about it here).

I have set up my mock Data tab as follows, but of course this method can be easily adapted:

enter image description here

You can see that I am listing all the ID-Category combinations and their corresponding value (I presume there are several Categories per ID).

Now to the main tab:

  • For the ID column a simple Data Validation can be done. You can select all the IDs in the Data tab, the duplicates will automatically be thrown out. This can easily be achieved by:

    =Sheet2!$A$2:$A$7

  • For the Category validation in the second column, an extra step is needed because natively populating drop-down lists (to dynamically adapt the Category drop-down to the current selected ID) is not [yet] supported. However, it can still be achieved will the following trick:

    enter image description here

    Where the formula used in the helper for validation is the following:

    =TRANSPOSE(FILTER(Sheet2!B$2:B$7,Sheet2!A$2:A$7=A2))

    and in the catergory data validation we have the following range:

    =G2:2

  • For the different values, we can again make use of the FILTER formula. You can paste this into the C2 cell and extend it to as may columns and rows as required:

    =FILTER(Sheet2!C$2:C$7,Sheet2!$B$2:$B$7=$B2,Sheet2!$A$2:$A$7=$A2)

Upvotes: 1

Related Questions