Data Uploader - status won't change if someone has made a manual change previously

I have a tracking sheet of client account numbers. We have one Status column that has options such as "Faxed" "Complete, waiting for download", " Complete", etc.

I have my formula set up so that the status column will change, and it works ONLY if no one has made a manual change to the status previously.

To clarify - we log something on the tracker and manually update the status to Faxed. Then a few days later I upload a file that lists all complete items. This item is on that list and so what should happen is the status will be updated to "Complete, waiting for download" or " Complete" (depending if a value is also present). Because someone has changed that field manually to Faxed, the status will not change. If someone would not have changed the status in the field at all then it updates appropriately.

I think I just need some part of my formula that says "no matter what the status is, update it to this"

Here's the formula just in case - =IF(AND(ISBLANK([Account Balance]@row), [DL?]@row = 1), "Complete, Waiting for Download", IF([DL?]@row = 1, "Complete", "Not Complete"))

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi Christina,

The issue here is that a column can either be driven by a formula or be manually updated. As soon as you make a manual change to a cell, it erases the formula beneath the cell content and can no longer automatically change to other values. (You can learn more about formulas in our Help Center, here.)

What you would need to do is to adjust your formula to account for the "Faxed" status and have that be one of its outputs. For example, you could add in a helper checkbox column or other type of column for when you need to manually say that it's been faxed. Then this formula can read that helper column along with your other criteria to output the correct status.

For example, this formula will say "Faxed" if the DL box is ticked and the helper fax box is ticked as well:

=IF(AND(ISBLANK([Account Balance]@row), [DL?]@row = 1, [Helper Faxed]@row = 0), "Complete, Waiting for Download", AND(ISBLANK([Account Balance]@row), [DL?]@row = 1, [Helper Faxed]@row = 1), "Faxed", IF([DL?]@row = 1, "Complete", "Not Complete")))

Should that be the wrong instance for when you want it to say faxed, I'd be happy to help figure out the logic statements in your nested if, we would just need to know what the criteria is for when each status should appear.

Hope this helps!

Cheers,

Genevieve

MCorbin

This is happening because someone manually updated the "Status" cell for that row, and by doing that, removed the formula. So even if your inload adds data, the formula no longer exists in that cell and can't update.

I prevent problems like that by locking the columns with formulas - so that users cannot change the values. I may also change the color of that column to call out that it is "special". You could consider hiding the column in the data entry sheet or not including it in a report so that the user doesn't even have it as an option to update.

If the user has Admin permissions, they'll still be able to change it, even if you lock the column.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.