Search code examples
if-statementgoogle-sheetsfiltercheckboxgoogle-sheets-formula

Use checkboxes to add cell information to a list in a different column

I have a master list of account codes used by my employer on a DatVal sheet for a budget tracker I used. I want to use checkboxes to select the most relevant codes to include in a list in a separate column that will become a data validated drop down list on another sheet.

I'm trying to use lookup and it sort of works, but is broken. I'm looking to see if the checkbox column is marked "True" then returning the result from a column where I've joined the account code and its description.

[Here is an image of the sheet.]1

I've set up an example sheet here: (https://docs.google.com/spreadsheets/d/1fxIfqjBjqTLMLRlyYAEy1j1aUn8vxyVwqgOKNpZoeZg/edit?usp=sharing)

And a table:

Relevant Account Codes Include in list Common Account Codes Account Code Descriptions Joined
Category 1
60017 - generic data 17 FALSE 60001 generic data 1 60001 - generic data 1
FALSE 60002 generic data 2 60002 - generic data 2
TRUE 60003 generic data 3 60003 - generic data 3
Category 2 -
TRUE 60004 generic data 4 60004 - generic data 4
TRUE 60005 generic data 5 60005 - generic data 5
FALSE 60006 generic data 6 60006 - generic data 6
TRUE 60007 generic data 7 60007 - generic data 7
60009 - generic data 9 TRUE 60008 generic data 8 60008 - generic data 8
TRUE 60009 generic data 9 60009 - generic data 9
FALSE 60010 generic data 10 60010 - generic data 10
FALSE 60011 generic data 11 60011 - generic data 11
FALSE 60012 generic data 12 60012 - generic data 12
FALSE 60013 generic data 13 60013 - generic data 13
TRUE 60014 generic data 14 60014 - generic data 14
Category 3 -
FALSE 60015 generic data 15 60015 - generic data 15
FALSE 60016 generic data 16 60016 - generic data 16
FALSE 60017 generic data 17 60017 - generic data 17

This is my first post on stackoverflow. Long time lurker. Any help will be appreciated. Thanks, I'm a pretty basic user of spreadsheets.

Solution

  • use:

    =ARRAYFORMULA(IF(B3:B=TRUE, E3:E, ))

    enter image description here

    or skip the blanks like:

    =FILTER(E3:E, B3:B=TRUE)