Automate the selection of values from a Multi-Select Dropdown

Pierre Mostert

Good day

I am trying to select values from a Multi-Select Dropdown by using an IF formula. Is this possible?

I dont want to manually have to select the applicable values from the multi-select dropdown selection, but instead the formula should select the multiple values in the dropdown.

Regards,

Pierre

Paul Newcome

The best way I have found for this would be to add in a helper row. It is your choice where you add it, but I am going to use Row 1 for my example on how to set it up.

In the helper row, you would enter each value you want populated for the checkboxes in the appropriate checkbox columns. So based on your screenshot it would look something like this (f is the formula to be provided)...

Completed Milestones..........Checkbox a..........Checkbox b..........Checkbox c

..................f.......................................a.............................b.............................c

..................f

..................f

..................f

Then the formula in the [Completed Milestones] column would pull from Row 1 (or whichever row you use for your "helper row") based on the boxes that are checked "@row".

=JOIN(COLLECT([Checkbox a]$1:[Checkbox c]$1, [Checkbox a]@row:[Checkbox c]@row, 1), CHAR(10))

Paul Newcome

It could be possible. Are you able to provide more detail as to your workflow? Screenshots with sensitive/confidential data removed, blocked, or replaced with "dummy" data would also be very helpful.

Pierre Mostert

Hi Paul

I have attached a screenshot.

Typically I would like the formula to select 'a' only when checkbox 'a' has been checked. Then when checkbox 'b' has been checked, 'b' should then be included as part of the completed milestone selection.

Thanks for the assistance.

Thanks!

Regards,

Pierre

Paul Newcome

The best way I have found for this would be to add in a helper row. It is your choice where you add it, but I am going to use Row 1 for my example on how to set it up.

In the helper row, you would enter each value you want populated for the checkboxes in the appropriate checkbox columns. So based on your screenshot it would look something like this (f is the formula to be provided)...

Completed Milestones..........Checkbox a..........Checkbox b..........Checkbox c

..................f.......................................a.............................b.............................c

..................f

..................f

..................f

Then the formula in the [Completed Milestones] column would pull from Row 1 (or whichever row you use for your "helper row") based on the boxes that are checked "@row".

=JOIN(COLLECT([Checkbox a]$1:[Checkbox c]$1, [Checkbox a]@row:[Checkbox c]@row, 1), CHAR(10))

Pierre Mostert

Hi Paul

Thanks for your response.

Yes it works great, thanks for all the assistance.

Just a question, is it possible to instead be selective which columns to look at, instead of a continues range of cells? So for example if my helper row is in a different reference sheet, I would need to keep both the sheet columns consistent else I will receive an #INCORRECT ARGUMENT SET.

Otherwise all works great.

Kind regards,

Pierre

Paul Newcome

@Pierre Mostert I'm not sure I understand what you're asking.

Pierre Mostert

Hi Paul

Thanks again for all the help. No worries about my previous question.

Kind regards,

Pierre