Formulas being converted to values

Alex McDaniel

Hi - I have two sheets where I have created formulas that return a percentage for child tasks based on hours estimated and date ranges that ultimately feed into the resource management view.

The formula works as expected (initially), but when I close the workplan and reopen, it will (at some point), convert the formula to the value, rendering the field static based on the last calculation.

Has anyone else run into this? Any recommendations

Formula: =IFERROR(IF(Hierarchy@row < Hierarchy54, "PARENT TASK", IF(ISBLANK([Updated Date]@row), [Hours Estimate]@row / ([Planned Duration]@row * 8), [Hours Estimate]@row / (([Updated Date]@row - [Planned Start]@row) * 8))), "INCOMPLETE DATA")

Note: The Hierarchy row has the standard hierarchy function, but the formula checks if there is a child task in the next row (e.g., row 53 would check "Hierarchy54", the subsequent line has "Hierarchy55", and so on) underneath it to avoid calculating "Parent" ow percentages (double counts).

Paul Newcome

Have you checked the Activity Log?

Alex McDaniel

Good recommendation. After digging through the activity log, I found that two things (following the initial paste-in of the formula:

1) FORMULA PASTE IN: 1/12 - 9:12 AM (Myself)

2) Calculated 0% converted to value: 1/13 - 2:42 PM (Myself)

3) Calculated 100% converted to value: 1/13 - 2:56 PM (Colleague)

I can attest for both my colleague and me, we did not go in and change the calculations to a value. Really stumped on what is going on with this.

Any other thoughts?

Andrée Starå

Hi @Alex McDaniel

Strange!

If you haven’t already, I would recommend that you reach out to the Smartsheet Support Team.

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Are you using update requests? How do you edit the row data?

Alex McDaniel

@Paul Necome: I do have some automated notices that are being sent out (task due, 2 weeks out, etc). Row data is primarily being edited through the % complete column and dates. Sometimes task names or dependencies are updated, but not nearly as often.

@Andrée Starå Yes - I have one submitted, however they are looking for a video which will be tough to replicate as you can see there is a gap of about a day and a half between when the formula goes in, and when it converts to a formula.

My suspicion is that it has something to do with the resource view function the calculated percent is being pushed to, but just a guess as this column has had several behavioral quirks when it comes to the calcs (e.g., occasionally conditional formatting does not apply, or I'll paste the formula over the entire column, but then parent rows wont actually "keep" the formula).

Genevieve P.

Hi @Alex McDaniel

It sounds like you're using this formula in the % Allocation column activated for Resource Views, is that correct? If so, formulas can't be placed in this column as the Resource View acts as its own type of formula (see the first bullet point in this Help Center article). When the sheet reloads to send that information to Resource Views, it removes the formula.

Please let our Product Team know about your feedback by filling in this form, here!

Cheers,

Genevieve

Genevieve P.

@Alex McDaniel

My apologies! I see that there was a recent release in October that should allow formulas in this type of column (see here). Please provide this information to Support, along with screen captures of your sheet, your Resource Management settings, and the type of column, if you are already troubleshooting this with them.

Paul Newcome

Could it be possible that these rows were updated through an update request? If that is the case and the cell is able to be edited in the update request, when it is submitted, it treats it as manual entry even if no change was made.