Creating a points sheet from a previous query (issues with formula/sheet referencing)

Options

Hi Everyone

I have kindly had an issue relating to scoring answers on a multi-select dropdown https://community.smartsheet.com/discussion/104030/subject-adding-weighting-points-to-answers-within-a-multi-select-dropdown#latest


However due to the volume of questions I'm looking to include, I want to create a separate points sheet. However I'm struggling to enhance the formula provided (=IF(Contains([2 question]@row, "type"), # value)) to support referencing the main sheet from the query, from a new points sheet.


Any support would be much appreciated.

Thanks Michael

Tags:

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Michael Stuart

    The way you could deal with your setup (based on your original post) is 1 of 2 ways.:

    1. Formula that is hard coded to each transport method as below (add for each one):

    =IF(HAS([2. Select all relevant modes of transport?]@row, "Car (-2 points)"), -2, 0) + IF(HAS([2. Select all relevant modes of transport?]@row, "Train (3 points)"), 3, 0)

    2. Keep a separate list of each transport method and the score. then your formula is:

    =SUMIFS({range of transport scores}, {range of transport names}, HAS([2. Select all relevant modes of transport?]@row, @cell))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!