What is responsible for these seemingly uncaused cell changes?

Nik Fuentes

I have a smartsheet mystery on my hands. A few people have looked at it and are now just as confused as I am, so maybe someone here has at least seen this before.

One of our cells, a very important cell on several provisioned projects, has been changing despite the cells that it is based on remaining static. Allow me to explain:

The cell "Total Revenue Earned" has the following formula:

"=SUM([Transaction Log Items Invoiced this Period]@row, [Trans Log Items Invoiced To Date]@row, [Carpenter Price to Date]@row, [Director Price to Date]@row, [Lead Designer Price to Date]@row, [Project Design Price to Date]@row, [Construction Planning Price to Date]@row)"

It's just a sum of 7 cells on a summary row. Straight forward enough. The cell history for this is showing multiple changes from the recent past. see the image below:

The confusing part is that all of the 7 cells that create that number have no history of any changes that correlate to the changes in Total Revenue Earned. As far as I am aware, this should not be possible. See the image below [also attached if the image quality isn't clear enough to make out]:

RevenueEarnrnedConstituentsCellHistories.png

These are the only 7 cells that have ever been a part of this formula. Only the first two component cells are linked in from another sheet, the rest are entered/calculated here. This has happened repeatedly across multiple, but not all, provisioned projects of this type. This has been completely inexplicable and as I'm sure you can infer from the name of the afflicted cell, this is a very important number that has a considerable impact whenever it changes.

To start with, does anyone have any idea what could even be causing this? It has to be something with the cross-sheet references, right? I suppose as a bonus, does anyone have any idea how to stop it?

Thanks,

-- Nik F

Paul Newcome

Does the sum of all 7 cells add up to the same amount when manually added together?

Nik Fuentes

Hi @Paul Newcome, Yes it does add up correctly as of now

Ella

@Nik Fuentes your source data is changing somewhere, maybe the first 2 that are linked from a different sheet. Are these 2 based on other formulas in the other sheet or are they static values?

Nik Fuentes

@Ella They're SUMIF() statements evaluating 4 collums [1 sum and 3 conditions] on a separate sheet in the provisioned folder. All 4 references are to the correct sheet at time of asking, at least.

Could those cells change without leaving a trail in the cell history? Or, more pointedly, only in a cell's history one step removed?

Ella

@Nik Fuentes thanks! If a cell changes, the change should be recorded in the cell history unless there's some glitch. Did you check the Activity Log for any changes in each sheet? Could it be a condition that is changing a cell value?

Nik Fuentes

@Ella

I tink it must have been a glitch then, as there was no cell history corresponding to those changes, nor in the Activity Log.

Frustrating. >:[

Scott Orsey

@Nik Fuentes @Paul Newcome

I have never seen this behavior. Your screen shots contain a few oddities. While the title says "Total Revenue Earned" there is a subtitle that reads "Total Invoiced to Date". I can't recreate the behavior of having a subheader like that. It's also odd to me that the most recent value in the history has a "," for the thousandths place and a decimal while the others don't. I'm also curious if the column is locked or if you are using a column formula. If not, then it could be possible for someone to manually overwrite the formula in that cell with another value.

Good luck as you troubleshoot. Be well