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

Michael Stuart

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

Leibel S

@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))