Cell History showing people making changes they did not make

dgloballab · 11/01/21

Recently we have had an issue where a cell keeps being updated and shows it was an individual user (multiple) only for those individuals, myself included, confirming we did not in fact edit that cell.

It is a formula driven cell. I have checked the cells used in that formula and those have not been updated.

Anyone know what could be happening? Example included where none of these team members made these changes today. As you can see it keeps going back and forth between two dates.

This is the formula in the cell that seems to be changing. =WORKDAY([First KOS Date]@row, -[SU Duration]@row) as stated the First KOS Date and the SU Duration have not been changed.

Genevieve P. · 11/02/21

Hi @dgloballab

Are either of the columns that this formula is referencing have a formula as well? ([First KOS Date] or [SU Duration]) Do either of them have the TODAY Function applied?

Whoever is opening/saving the sheet is "updating" this cell because they are causing the formula to re-read the two cells and re-calculate in case there have been changes. I ask about the TODAY function because there's a specific list of actions that will update TODAY (and therefore "update" the cell, even if no changes to the value were made). See: TODAY Function

Cheers,

Genevieve

dgloballab · 11/02/21

Hey Genevieve,

Are either of the columns that this formula is referencing have a formula as well? ([First KOS Date] or [SU Duration]) - Both have formulas

First KOS - =MIN([C Clinic KOS]@row, [Y Clinic KOS]@row, [G Clinic KOS]@row, [M Clinic KOS]@row, [H Clinic KOS]@row)

SU Duration - =IF(Complexity1 = "Low", 20, IF(Complexity1 = "Medium", 25, IF(Complexity1 = "High", 35, 99)))

Do either of them have the TODAY Function applied? No

I am not sure I follow what you are saying. Why would it show that all of us updated a cell? What is interesting is it is not happening to the entire sheet just some rows and there isnt a pattern that i have found.

What do you mean by "Whoever is opening/saving the sheet is "updating" this cell because they are causing the formula to re-red the two cells that re-calculate" even if that were true there is no reason for the date to actually change back and forth like that for some of the other cells in different rows right?

This is causing us major issue as dates are changing that should not be.

dgloballab · 11/02/21

adding another screenshot. last night i added a Modified column to capture when there are mass updates like this.

See attached. This seems system driven not user driven. And i am not sure why it is only happening to some rows and not all.

Genevieve P. · 11/02/21

Hi @dgloballab

Ok we'll need to keep digging. For First KOS formula, are any of these cells using a formula and have TODAY?

[C Clinic KOS]@row,

[Y Clinic KOS]@row,

[G Clinic KOS]@row,

[M Clinic KOS]@row,

[H Clinic KOS]@row

For the rows that are changing, is it possible that the MIN function is calculating one date first, then updating to a second date? (Which would create a domino to update your WORKDAY function back and forth).

Can you check the First KOS cell History to see if the same changes are happening in these cells for those rows?

dgloballab · 11/02/21

Ok we'll need to keep digging. For First KOS formula, are any of these cells using a formula and have TODAY? No formulas in the below cells

[C Clinic KOS]@row,

[Y Clinic KOS]@row,

[G Clinic KOS]@row,

[M Clinic KOS]@row,

[H Clinic KOS]@row

For the rows that are changing, is it possible that the MIN function is calculating one date first, then updating to a second date? (Which would create a domino to update your WORKDAY function back and forth).

Can you check the First KOS cell History to see if the same changes are happening in these cells for those rows? - The First KOS date does not show any recent changes in the cell history.

Genevieve P. · 11/02/21

Great! So we've eliminated one possible cause. Let's check the other side of the formula then.

What about these two columns: SU Duration & Complexity

Does Complexity have a formula?

Did SU Duration record a change back and forth at all?

dgloballab · 11/02/21

The formula that is being used in the Due Date (GLSD Signature) is the below; could it be an issue with the workday?

=WORKDAY([First KOS Date]@row, -[SU Duration]@row)

Genevieve P. · 11/02/21

Hi @dgloballab

It could have to do with either of the cells that this WORKDAY formula is referencing, yes. We've identified that it isn't the [First KOS Date]@row that's causing updates, so now we need to look at [SU Duration]@row.

It could also be the - sign in front of your value.

Does Complexity column house a formula?

Did SU Duration record a change back and forth at all in the Cell History for this column?

dgloballab · 11/02/21

Complexity - drop down selection hasnt changed

SU Duration does not show a back and forth change

Genevieve P. · 11/02/21

Yes exactly.

So the SU Duration column is referencing the Complexity column.

Does Complexity have a formula as well? How do you determine if something is Low, Medium, or High?

Genevieve P. · 11/02/21

https://community.smartsheet.com/discussion/comment/309643#Comment_309643

Does that mean the dropdown is a manual selection?

If so, then the final element of your WORKDAY formula is indeed the - sign before the referenced cell. Try having the negative symbol in the value itself instead of before the cell reference:

=IF(Complexity@row = "Low", -20, IF(Complexity@row = "Medium", -25, IF(Complexity@row = "High", -35, -99)))

dgloballab · 11/02/21

drop down is a manual selection.

What you suggested about the changing complexity formula isnt working for me.

Is there a better way to write the workday one?

=WORKDAY([First KOS Date]@row, -[SU Duration]@row)

My goal is take the First KOS Date and subtract the SU Duration. Then I only want it to account for working days and not weekends. Maybe I need to write that differently? Because it does only seem to be flipping back and forth on dates that surround a weekend.

dgloballab · 11/02/21

changing my formula to remove the "-" does not help me at all. in fact it totally throws the timelines out of whack.

BEFORE CHANGE formula =WORKDAY([First KOS Date]@row, -[SU Duration]@row)

AFTER CHANGE formula without "-" =WORKDAY([First KOS Date]@row,[SU Duration]@row)

What is another solution to get the results I am looking for;

My goal is take the First KOS Date and subtract the SU Duration. Then I only want it to account for working days and not weekends. Maybe I need to write that differently?

Genevieve P. · 11/03/21

Hi @dgloballab

My apologies, I should have been more clear!

Since [SU Duration]@row is taking the number from the Complexity formula, and making it negative, I thought it may be the - sign in the current formula causing issues. If so, we could make the output of the [SU Duration]@row be negative, instead of adding - in front.

So, yes, use:

=WORKDAY([First KOS Date]@row, [SU Duration]@row)

But only if the output in the other formula is negative:

=IF(Complexity@row = "Low", -20, IF(Complexity@row = "Medium", -25, IF(Complexity@row = "High", -35, -99)))

Or try multiplying it by a negative 1 instead:

=WORKDAY([First KOS Date]@row, ([SU Duration]@row * -1))

WORKDAY is the correct function to use for what you're looking to do.

Cheers,

Genevieve

dgloballab · 11/03/21

Hey @Genevieve P. i am going to try this on one of the rows in my file and watch it for the toggling back and forth between dates. the issues appears to be getting worse and editing at least every hour. I have turned on the highlight function to let me know when it changes. Again no one on the team is acutally editing cells.

Will report back tomorrow.

Cell History showing people making changes they did not make

Answers

Categories