Formula is not persisting when value is removed from cell

Hello,

We are having an issue with cell formulas. The cell formula is erased when we change or delete a value in a cell.

Here is the formula we are using:

=IF(Status4 = "Complete", "In Progress")

The formula should first check the cell status in Row 4, then change the cell status on the row where the connected cell is located.

The cell statuses to choose from are: Not Started, In Progress, Complete, Cancelled.

When the status is changed to anything other than "Complete," or if someone deletes the value in the cell, then the formula is also deleted.

This is strange behavior and very troubling. Can someone assist?

Sincerely,

Jess Cafarelli

Find more posts tagged with

Sheets

Formulas

Support

functionality

Accepted answers

All comments

Jon Mark H

Hey Jess-

Can you show a screenshot of what's effected by this (minus any sensitive data, so I can understand better?

Are you using multiple sheets or is this all on the same sheet?

Hope I can help!

-Jon Mark

jcaf

Hello Jon,

Thank you so much for looking into this with us!

To answer your question, we are using one sheet.

Here is a screenshot of the formula (I changed the Task Names to something generic to show you more of the sheet):

Screen Shot 2022-03-29 at 8.32.14 AM.png

That formula is set on each of the Status cells, referring to a predecessor.

Now if I decided that the Store needs more cleaning, even though row 43 it is marked as Complete:

Screen Shot 2022-03-29 at 8.34.24 AM.png

And then I delete Complete, or change it to Not Started, the whole formula gets erased, too.

Screen Shot 2022-03-29 at 8.36.33 AM.png

I am not sure why this is happening. The sheet is saved with the formulas. However, anytime a Status is changed, for example if a status really wasn't completed, then the formula is erased, too.

Please let me know if you need more information.

I appreciate your time and help!

Sincerely,

Jess Cafarelli

Jon Mark H

Okay - thanks for sharing the screenshots - that helps a lot for me to understand!

So a couple of things - yes, you're deleting the actual formula when you manually change the status in that column. Basically you must create another column to hold your formula, then you can automate this "Complete" part, but still be able to change it.

What I would do, which would keep some space

1.) Create a Helper column That's a checkbox

2.) In that column, you'll basically have the formula you already have, but so that it works for a checkbox:

=IF(Status42 = "Complete", true, false)

(this checks the box when Status42 changes to "Complete")

3.) Setup an Automation - which, when a box in this column get checked (via the formula),

It changes that row's Status to "In Progress"

It takes a second & sometimes you need to refresh the sheet for it to show this change.

There are some limitations to this method -

You still have manually put in what row to look at, as you did with your previous formula (Status42),

If you deleted the status in row 43 now & want to change the status, you can.

If you want it to populate with "In Progress" automatically again - just change Row 42 to ANYTHING other than "Complete", save the page, then change Row 42 back to "Complete" again - though in this case it may be simpler to just manually change Row 43 to "In Progress" yourself.

This is something you'll have to watch out for as well - if you use this method - if Row 43 "Clean Store", WAS complete, but Row 42 "Put Out Display Items" was NOT for some reason - you change Row 42 to "In Progress", then complete it & change Row 42 to "Complete" - the automation will run again & change Row 43 to "In Progress" again.

If that's a problem, let me know, there's some work arounds you could do!

Does this other column & automation solve your problem?

Let me know if this doesn't fulfill your needs and let me know any tweaks you have in mind!

-Jon Mark

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.