Formula Creation & Random Changes

Terry P

Hi All,

I'm building a reference Sheet off of a 'Master' Smartsheet in order to create a Dashboard for some KPI type tracking.

I'm pretty OK with formulas, the main ones I'm using here are simple =SUMIFS() and -COUNTIFS()

Example:

=SUMIFS({TYS Master Data - From SFDC Range 1}, {TYS Master Data - From SFDC Range 3}, =[Column4]2, {TYS Master Data - From SFDC Range 4}, <[Column4]1)

This works fine.

My lesser issue is that when I'm building a formula using the inbuilt generator if you click 'reference another sheet' the cursor sometimes comes back the wrong side of the ) and or } - this I can spot and deal with.

My major problem is every now and then if a build a new formula Smartsheet decides to change all my other formula and I pretty much have to start again, it's massively frustrating!

I'm not copying cell contents to one place or another so there should be no underlying links.

It seems to briefly say "updating References"

Has anyone else experienced this?

Anyone have any insights?

Terry P

OK - I've studied a bit and figured out the the Range doesn't necessarily relate to the column I'm referencing, it's a reference to the number of time the Master Sheet has been erferenced - I need more coffee!

So the sample formula above now reads (some references changed to protect the innocent ;-))

=SUMIFS({TYS Master Data - Number of things}, {TYS Master Data - Year Added}, = [Column4]2, {TYS Master Data - From SFDC Month Added}, <[Column4]1)

Easier to read but still doesn't work :-(

Paul Newcome

What exactly do you mean when you say it "doesn't work"?

Terry P

Sorry, should be more clear ;-)

{TYS Master Data - "Number of things"} is a text/number column containing a series of ID numbers

{TYS Master Data - Year Added} is a text/number field containing the year extracted from an =TODAY() cell in TYS Master Data

[Column4]2 is a cell on the sheet I'm setting up the metrics on, like the above it is a year extracted from =TODAY() but on the metrics sheet

{TYS Master Data - From SFDC Month Added}, <[Column4]1) is the same as the above but for the month number - the < is because I want to track to the end of the previous month

The full formula

=SUMIFS({TYS Master Data - Number of things}, {TYS Master Data - Year Added}, = [Column4]2, {TYS Master Data - From SFDC Month Added}, <[Column4]1)

The result is 0, if I manually highlight the same dataset in the Master Sheet it should be 486. The completely weird thing is this formula worked until I entered another formula elsewhere on the 'metric sheet' at which point it (and others) all went to 0 - a brief note on the screen mentioning cell reference updates.

Any thoughts would be great!

Paul Newcome

What was the formula you added? Where there any cross sheet references in that formula? How exactly did you create this new formula? If using cross sheet references and copy/pasting/tweaking from another formula, did you adjust references by clicking on "Edit Reference"?

Terry P

Hi Paul - and thank you for taking a look at this

There are 40-45 cross sheet formula on the 'Metrics Sheet' - a good 20+ were working until I entered one that seemed to change the references.

I have typed each formula in separately, thinking that cut and paste might be the issue, it seems not.

I have tried/used the Edit References but still get 0 returned.

I started renaming some of the references last time, {TYS Master Data - Year Added} for example.

Any suggestion you have would be great, I'm going to nuke the sheet and create it again and see what happens.

The worst bit is if the system does change the references and the formula start to fail the 'undo' button doesn't get me back :-(

Have a great day Sir

Cheers

Terry

Paul Newcome

It is most likely going to boil down to the "Edit Reference" piece. Editing a reference will update all of those references across the sheet. If you want to use a different range, you need to create a new range and not edit an existing one. If you do edit in order to rename, be very careful not to accidentally change the referenced area.

Terry P

Oh Joy.

Thanks Paul, the really is huge help, knowing what to look out for!

Do you think naming the references in the format {TYS Master Data - Year Added} will help?

If {TYS Master Data - Year Added} stays consistent to that column reference might I get away with it?

Have a great day!

Terry

Paul Newcome

I always name my references in a very similar manner. That should help keep things organized for sure.