Need a formula to recognize a checkbox and match it to a name

Amy Mosley

I work in a property mgt department and we've built a room inspection form. Our suites have rooms assignments based on the bedroom within the suite. The columns for the form are broken into sections of columns but I have the rooms assignment rosters broken down into rows. I need a formula that will recognize the box is checked for a failed safety inspection item and the names of the location to match the roster sheet.

This is a screenshot sample of our inspection sheet with the form, I've organized the columns it into sections of bed assignments "bed A", "bed B", "bed C", "bed D". The screenshot is condensed down to show bed A and bed B because the form is long.

In the roster, I need the columns to recognize the rooms assignments in the roster column in the rows. I basically need it to move from horizontal to vertical so I can do a document generator.

I've know it would be easier formula wise to maybe parent/child the bed assignments on the Building Roster sheet to but I can't figure out what formula would be best. I tried a if/countifs formula but it's not checking the box and recognizing the name. I also tried building a countifs/contains but I couldn't make it work either.

I hope this makes sense. I appreciate any help.

Jason Tarpinian

Try using an INDEX/MATCH combo. Using the same named ranges you have listed above, try re-formulating it as

=INDEX({Pet - Bed A}, MATCH([Room Space Description]@row, {Inspection Option 2 Range 2- bed A},0))

This will return the FIRST match found on your form. So if that is being populated using a form, make sure that there is the option that new entries appear on top so that the INDEX function grabs the correct row. There is a way to adjust the formula otherwise, but adjusting the form option will save you from that!

Julie Fortney

Hi @Amy Mosley

I think your best bet would be to create a helper column on your roster sheet.

You could call it Bed, with this formula:

=RIGHT([Room Space Description]@row, 1)

This will pull the right-most character in the text, which is the bed letter.

You could then use that helper column to tell your formulas where to reference data. Here's an example for the Pet column:

=IF(Bed@row = "A", INDEX({Building Safety Inspections Pet - A}, MATCH([Room Space Description]@row, {Building Safety Inspections Bed A Assign}, 0)), IF(Bed@row = "B", INDEX({Building Safety Inspections Pet - B}, MATCH([Room Space Description]@row, {Building Safety Inspections Bed B Assign}, 0))))

If the Bed column is "A," the formula will run an index/match to pull the value from the "Pet - A" column on your inspections sheet. If the Bed column is "B," the formula will run an index/match to pull the value from the "Pet - B" column on the inspections sheet. And so on...

Note I only wrote the formula for the A and B beds, so you'd need to continue on for C and D.

Let me know if that helps.

P.S. Do you work in student housing? I did, years ago, and this brought back memories. Oh, the stories we could tell. :)

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

Amy Mosley

@Jason Tarpinian and @Julie Fortney - Both of these worked. Thank you so much for taking the time to do this! My go to formula is Index/Match but for some reason, I didn't think it would check the box without an If statement included. I tried it but I didn't do it correctly. I also haven't tried the Right formula before and so this is new to me. I have still have a long ways to learn on formulas. Now it's just deciding if I want to parent/child or build a longer formula and just convert the whole column.

Thank you both again!