Metrics sheet - smartsheet limitations and quirks

Rob Wright

This is a bit of an open question and I will try to make sense;

I was making a metric sheet, made a set of metrics using a formula (iferror, avg, collect in one formula etc). Then I wanted to do another, with a similar formula, based on a different criteria range. So i copied the formula somewhere else in the sheet with the intention of then just changing the criteria range and the criteria in the formula, and then copying down. As soon as I tried to change the criteria, it broke the entire first set of metrics I made, which were then fixed when I clicked into one of the formulas in that range, edited the formula and selected the correct criteria range for it (which was still set correctly anyway?!!!).

I seemed to get more joy when I manually entered my new formula rather than copying, pasting and amending, and this then didn't break anything.

Can anyone give some kind of advice on the kind of limitations smartsheet has when building a metrics sheet compared to being able to use excel more freely. For instance, is there a quirk where you can't copy formulas as I have above to make a new set of metrics without breaking the original. Why does conditional formatting apply to a whole column when I have only selected specific cells, and does that mean I have to put different metrics with different conditional formatting in different columns. I would appreciate the answers to this, and any other pitfalls I have to watch out for while making metrics for dashboards

Many thanks in advance

Paul Newcome

When you went to update the formula, did you adjust the search range? If so, how exactly did you do this? If you are copy/pasting/adjusting a formula that has a cross sheet reference, you will want to first REMOVE the cross sheet reference from the new formula then create the new one.

If you click into the portion of the formula that has the range and then select the option to "edit" the cross sheet reference, when you update the range it will update to that range in every instance of that cross sheet reference on your sheet.

So if you set up {Range 1} in one formula, copy/paste it to another cell, then EDIT {Range 1}, it will update to the new range in the first formula as well because that cross sheet reference also has the same name. You would want to remove {Range 1} completely from the second formula and insert a brand new cross sheet reference.