Output combined column name values from checkboxes.

jambla

Hi,

I have a number of checkboxes, if they are checked I want to output the column value in another cell.

I understand we can't reference the column values but I am able to do this:

=IF([Red]1, "Red")

This will return a single value if the checkbox is true.

When I try something like this:

=IF([Red]1, "Red", IF([Green]1, "Green"))

I can only output a single value, in this case Red or Green.

I would like to be able to output like my image above.

Thanks for the help!

Andrée Starå

Hi,

Try this.

I'd recommend adding the column names in the first row (or where it makes sense) on the sheet, and that would make it easier to use the formula below.

=JOIN(COLLECT($Red$1:$Blue$1; Red@row:Blue@row; @cell = 1); "/")    

The same version but with the below changes for your and others convenience.    

=JOIN(COLLECT($Red$1:$Blue$1, Red@row:Blue@row, @cell = 1), "/")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

jambla

Worked perfectly! I never thought about the workaround to add a row with the values, Genius!

Thanks Andrée!

Paul Newcome

This is actually my preferred method. 

Andrée Starå

Happy to hear that it works!

It's an excellent workaround that I picked up from Paul ⬇️

I'm always happy to help!

Best,

Andrée

Paul Newcome

How does that saying go...?

 

Necessity is the mother of invention? I NEEDED to quit banging my head against a wall using a bunch of helper columns, so I figured out a way to cut them out. Haha. You should have seen some of my earlier attempts. It was messy to say the least.

mahmadi

what does @cell refer to? I am trying to bring the values and separate them by comma. I am working on an example with two columns but will need to extend it to 8 columns. for now I tried using this but it says invalid operation: (currently I have the visa column after that there is the flex column and I want the response to reflect in SC column below)

=JOIN(COLLECT($Visa$1:$Visa$1, $flex$1:$flex$1, Visa@row:flex@row, @cell = "Yes"), ",")

Paul Newcome

@mahmadi Try this instead:

=JOIN(COLLECT(Visa$1:Flex$1, Visa@row:Flex@row, @cell = "Yes"), ",")

mahmadi

Thank you @Paul Newcome . how would this work for my other formula below? in this scenario, I have a sheet where I am want to get only people who's term date matches the one I have in this new sheet but on not on LOA and are on notice leave . so three conditions. currently, I am bringing back the staff name only. I want to bring more details about them like their ID, Address, etc. the intention behind it is to create a workflow to send an email to a contact each day and the list for each day will be different since different people will have met the criteria. I can do it in the main sheet but it will send row by row and I want it to Daily send one email with the details of those staff. can you kindly help?