Search code examples
google-sheets

How to create a dynamic list, based on checkboxes? [Google Sheets]

I will try to be as descriptive as I can in what I am trying to do, bear with me, it's quite long.

I have two Google Worksheets - one is called "Source" and the other is called "Work". In the "Work" sheet, there are two tabs, one is called "Project" and the other is called "Countries". I am using a =IMPORTRANGE function to get a list of countries from the "Source" sheet into the "Work" sheet, "Countries" tab. There are multiple lists that are getting imported from the "Source" sheet, so I am using multiple =IMPORTRANGE functions. Let's call these lists "ListA", "ListB" and "ListC" ect.. The reason I am using =IMPORTRANGE here, is because this list is dynamic and will change monthly, which gets updated in the "Source" sheet.

In the "Project" tab, I have numerous checkboxes. Let's call them "Project A", "Project B" and "Project C" ect.. When I click on the checkbox, the value of it changes from "FALSE" to "TRUE".

My goal: In the "Project" sheet, I have a company, called "Company 1" in the E column. In the E column, below, I click the checkboxes that correspond to "Project B" and "Project E". I then am trying to achieve, that in the "Countries" tab, E column, below the same "Company 1" I would get a single list of countries, that are listed in "ListB" and "ListE", alphabetically, and without any duplicates or spaces.

Here is the example sheet - https://docs.google.com/spreadsheets/d/12A3U9W1xoktWbMNe2znZnuPPfgUlLXk5kQR29Z3Pj2k/edit?usp=sharing any place that formulas are, I have highlighted with a black border. I will place how I would like the result to look in the "What I Want" sheet.

I want it so that when I click the checkboxes for whatever projects in the "Project" tab, the corresponding list of countries would appear below the company name in the "Countries" tab, for that list to be alphabetically sorted and for it to only display unique countries.

I tried using the =UNIQUE and =UNIQUE(FLATTEN functions but I kept getting spaces between the lists, and the text was not sorted alphabetically anyways.

Solution

  • If the order of the projects in "Projects" and the order of lists in "Countries" are the same, you can use FILTER to choose the columns, TOCOL to make it a list without spaces, UNIQUE to get only once each one, and finaly SORT:

    =SORT(UNIQUE(TOCOL(FILTER(H2:O,TOROW(Project!E18:E25)),1)))

    enter image description here

    TOROW was used to convert your vertical list into the horizontally displayed values