Help referencing checked boxes in another sheet

prtrooper

I am currently trying to pull information from an intake form directly into a project task sheet based on which boxes the user checks for individual project needs. The form has an automatic number field that sets the project request number, and the goal is to be able to simply add that request number to the correct cell in the project sheet and have the rest of the information populate automatically.

I have set the intake form up in two ways: one as a multi-select dropdown menu based on request category, and another as individual check boxes for each choice which appear via logic based on the category.

I was struggling to get individual values from multiselect drop-downs to appear, so I switched to the checkbox method, which seemed more straightforward but has turned out to be just as difficult. I just need to get these formulas correct once so that I can save a template for the project task sheet, but I am stumped. Everything I do comes back as invalid or unparseable.

I've tried VLOOKUP, which is perfect for all the other cells from the form submission, but I can't seem to pull data and have it appear properly for any checked boxes.

This is the formula I've used successfully on other parts of the sheet: =VLOOKUP(Task1, {CLS Marketing & Media Request Range 2}, 24, false)

My most recent attempt for the checkboxes was a combo of IF, AND, CONTAINS to try to identify the row by request number and then pull value info from the first row with check boxes.

=IF(AND(CONTAINS(Task@row, {CLS Marketing & Media Request #}), {CLS Marketing & Media Request - Flyers} = 1), "Yes", "No")

If I need to get rid of the checkboxes and turn them into dropdown menus, I can do that, but I was having trouble doing the same with multi-select dropdowns.

Any help would be appreciated.

Samuel Mueller

@prtrooper

IF(countifs({CLS Marketing & Media Request #}, Task@row, {CLS Marketing & Media Request - Flyers}, 1) >0, "Yes", "No")

Try something like this

Samuel Mueller

If you are evaluating Multi Select Drop down fields, you should use the HAS function

prtrooper

@Samuel Mueller That got me closer! It's returning an answer now, but unfortunately, the answer is the same regardless of whether the box is checked in the referenced sheet. (I looked to be sure there were some checks in these selections.) Any suggestions of tweaks I could make?

The only change I made to the formula you suggested was to switch Task@row to Task1. Then even tried adding the formula to the same row as the request number to be sure that wasn't a variable that was messing with the results.

Samuel Mueller

@prtrooper i'm a little confused at what you are trying to return, can you send a screenshot of the referenced sheet and maybe highlight the value you are wanting to return?

prtrooper

https://community.smartsheet.com/discussion/comment/448037#Comment_448037

For the example above, I'm trying to return Yes/No (or 0/1, or really any variation of two distinct choices) based on whether the box is checked. You can see in the screenshots below—which show the intake data that corresponds to the design tasks for project "Testy Test," which all came up as 'no' values using the COUNTIFS formulas—that the top row (request #0001) does have three of the first four boxes checked. I replicated the formula you suggested for each task and adjusted the second column range accordingly. It returned 'no' regardless of whether the box was checked.

First few columns from reference sheet:

Further down the row where the task data from the form is represented:

This is what returns:

I also tried the HAS function as you suggested to try to pull info from the other version of the form and corresponding reference sheet that is built using multiselect dropdown.

That reference data looks like so:

The corresponding result on the task tracker was #unparseable. This could be that I haven't gotten the hang of nesting functions, but I tried several variations of this one to no avail.

Any guidance on where I'm going wrong would be HUGELY appreciated!

Samuel Mueller

For the first scenario, if your column references are right, and your task 0001 is in the same row where the flyers box is checked, then that formula should have worked.

try this

=countifs({CLS Marketing & Media Request #}, Task1, {CLS Marketing & Media Request - Flyers}, 1)

and you should have the value 1 returned.

also try this if that doesn't return 1, sometimes you need @cell

=countifs({CLS Marketing & Media Request #}, @cell = Task1, {CLS Marketing & Media Request - Flyers}, @cell = 1)

For the alternate function, it should look like this

=if(countifs({Marketing & Media Requests Range 2}, has(@cell, "T-Shirts"), {Marketing & Media Requests Range 1}, @cell = Task2)>0,"Yes","No")

Try some of these combinations and let me know. If the first countifs return 1, then wrap it in that if statement where if >0 return yes