CODEWITHSUNDEEP
google-sheetscheckboxgoogle-sheets-formulaformula
Antor Cha
Antor Cha

Reputation: 83

Create a list depending on the checkboxes

I have a list of industries and sub-industries of a sector and a list of several companies with their sector, industry, and sub-industry.

Until now I've filtered depending on one parameter (in this case sub-industry). This is the formula:

=FILTER ( J2:M12 ; M2:M12 = filter ( H2:H8 ; G2:G8 = TRUE() ) )

enter image description here

I want to filter the company list depending on if the checkbox is true or false. If several checkboxes are selected, show in the list these companies, only uniques values.

If it is possible do not use query function due I want to maintain future hyperlinks.

For example, if I only select Energy's checkbox, it will only display all Energy companies. If I only select Oil Gas & Consumable Fuels' checkbox, it will only display all Oil Gas & Consumable Fuels companies.

But if I select both Oil Gas & Consumable Fuels and Energy Equipment & Services, it will display these companies. Or selecting different sub-industries, but the aim is to display the companies that are in the selected groups.

Thanks in advance, if something isn't clear let me know.

Example's Spreafsheed: https://docs.google.com/spreadsheets/d/1c9cp0J4m1M-HbnDr_TliknuPSXHgdqkuuzAvzK75zC8/edit?usp=sharing

Upvotes: 0

Views: 505

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9355

I do not recommend having your filter checkboxes and filter results on the same page as your raw data. It will box you in, making it hard to add new items to the raw-data list and requiring changes to formulas. It will also bump your results down further and further as you add new items to the full list, which will not be convenient for you.

I have added two new sheets to your sample spreadsheet, with both tabs highlighted in bright green. These two sheets work together. They do not reference your original sheet at all.

The "Todos" sheet would keep your full list of raw data. The "Filtrar" sheet is where you will select options and see the filtered results.

The one formula is in Filtrar!J2:

=ArrayFormula(IFERROR(FILTER(Todos!A2:D;Todos!A2:A<>"";VLOOKUP(Todos!B2:B;{B2:B\A2:A};2;FALSE);VLOOKUP(Todos!C2:C;{E2:E\D2:D};2;FALSE);VLOOKUP(Todos!D2:D;{H2:H\G2:G};2;FALSE))))

This formula filters the full "Todos" list with one condition that rules out blank rows, followed by three VLOOKUP conditions, each essentially the same, i.e., looking up specific criteria in the filter list and returning whether or not it is TRUE. (You don't see the word TRUE in the formula, because the only results possible for each VLOOKUP are either TRUE or FALSE since those are the only possibilities for checkboxes.)

As you'll see, I also added some formatting to the "Filtrar" sheet. Simple, clear formatting can make data much easier to read.

You also did not need the redundancy in the checkbox label columns, so I removed those.

Upvotes: 1

Jason E.
Jason E.

Reputation: 1221

I tried replicating your current formula and added in the FILTER to have multiple conditions in it. Please see formula below:

=FILTER(J2:M12;
IF(COUNTIF(A2:A8; TRUE)>0;MATCH(K2:K12;FILTER(B2:B8;A2:A8);0);N(K2:K12)<>"");
IF(COUNTIF(D2:D8; TRUE)>0;MATCH(L2:L12;FILTER(E2:E8;D2:D8);0);N(L2:L12)<>"");
IF(COUNTIF(G2:G8; TRUE)>0;MATCH(M2:M12;FILTER(H2:H8;G2:G8);0);N(M2:M12)<>""))

By default, this will display all of the data in the table if there is no item checked in the checkboxes available. Please let me know if this solves your problem.

Upvotes: 0

Related Questions