Formula continuously updates cell even though values are not changing

Certain combinations of results are continually updating even though the value/result didn't change. Every time I open the sheet, it requires me to save but I didn't change anything.

Combinations with issue: "Utility, Telecom, Waste" and "Utility, Telecom"

Combination without issue: "Utility, Waste"

Services column is multi-select.

=IF(AND([Utility Team (Vertical)]@row <> "", [Utility Team (Vertical)]@row <> "Product Terminated"), IF(OR([Telecom Team]@row = "", [Telecom Team]@row = "Product Terminated"), IF(OR([Waste Team]@row = "", [Waste Team]@row = "Product Terminated"), "Utility", "Utility" + CHAR(10) + "Waste"), IF([Waste Team]@row = "", "Utility" + CHAR(10) + "Telecom", "Utility" + CHAR(10) + "Telecom" + CHAR(10) + "Waste")), IF(AND([Telecom Team]@row <> "", [Telecom Team]@row <> "Product Terminated"), IF([Waste Team]@row = "", "Telecom", "Telecom" + CHAR(10) + "Waste"), IF([Waste Team]@row <> "", "Waste", "")))

Find more posts tagged with

Formulas

Multi-Select Dropdown

Accepted answers

jmyzk_cloudsmart_jp

@Ami Veltrie

Thanks for the follow-up — your observation actually supports something I was guessing.

The reason “Utility, Waste” works but “Utility, Telecom, Waste” doesn’t is likely because the former is already in alphabetical order, so Smartsheet doesn’t appear to reorder it. But when the values aren't in alphabetical order, Smartsheet may internally sort them before applying dropdown list order — which can lead to inconsistencies in reporting or filtering.

From what I can tell, Smartsheet:

Sorts values alphabetically by default.
Then, if a dropdown list is defined, reorders based on that list for display.
But cell history or filters may still reflect the original alphabetical sort.

That’s likely why filtering by “Utility, Telecom, Waste” doesn’t behave as expected — it doesn’t match the internal representation Smartsheet is using.

As for the cell history not updating: if the new formula result is the same as the current value, Smartsheet won’t log a new change — but the yellow highlight still appears due to sheet activity.

Honestly, understanding Smartsheet’s internal logic here is only possible by testing different cases — or asking the people who built it. Let me know if you want to try prefixing values (like “1-Utility”) to force sort order and improve filtering/reporting behavior.

Ami Veltrie

@jmyzk_cloudsmart_jp Thank you for that additional information.

I reordered the drop down and the formula. This formula solved the problem:

=IF(AND([Telecom Team]@row <> "", [Telecom Team]@row <> "terminated"), "Telecom" + CHAR(10), "") + IF(AND([Utility Team (Vertical)]@row <> "", [Utility Team (Vertical)]@row <> "terminated"), "Utility" + CHAR(10), "") + IF(AND([Waste Team]@row <> "", [Waste Team]@row <> "terminated"), "Waste", "")

All comments

jmyzk_cloudsmart_jp

Hi @Ami Veltrie

I guess you might be noticing that Smartsheet keeps reordering your values in a multi-select dropdown column. That usually happens when the column is set to multi-select — Smartsheet tries to match and display values in the same order as defined in the dropdown list settings, not necessarily in the order you write them in the formula. So even if your formula outputs something like “Telecom” then “Utility,” Smartsheet may still show them as “Utility, Telecom” if that’s the order defined in the dropdown list.

Here’s something interesting I’ve observed:
Smartsheet seems to treat multi-select dropdown values as follows:

If the column has no dropdown list values defined, Smartsheet sorts selections alphabetically.
If a list is defined, Smartsheet sorts the selections based on the dropdown list order, not alphabetically.
However, in the cell history, it often shows the values in alphabetical order regardless.
So I guess Smartsheet internally first sorts values alphabetically, checks if a dropdown list is present, and then reorders based on that list when displaying it in the cell.

If you want full control over display order and line breaks, switching to a Text/Number column is a good workaround — Smartsheet won’t apply any sorting or validation there, and the formula result will appear exactly as written.

Here’s the updated formula written in the order: Utility, Telecom, then Waste — to match the dropdown list configuration:

=IF(AND([Utility Team (Vertical)]@row <> "", [Utility Team (Vertical)]@row <> "Product Terminated"), "Utility" + CHAR(10), "") +
IF(AND([Telecom Team]@row <> "", [Telecom Team]@row <> "Product Terminated"), "Telecom" + CHAR(10), "") +
IF(AND([Waste Team]@row <> "", [Waste Team]@row <> "Product Terminated"), "Waste", "")

https://app.smartsheet.com/b/publish?EQBCT=5780676fbda0428d9aca05fcbbec92cf

Ami Veltrie

@jmyzk_cloudsmart_jp Hello and thank you for your help with this!

I tried the formula but the result didn't appear to be distinct enough (three distinct values) for reporting or even filtering on the sheet itself.

Can you help me understand why this isn't an issue for the "Utility, Waste" combination?

Can it be solved if I change the sort order of the dropdown or the formula?

Strangely, since I replaced my formula with yours, it isn't actually showing an update in the History anymore but the cell is highlighted yellow (indicating a change in the last hour).

jmyzk_cloudsmart_jp

@Ami Veltrie

Thanks for the follow-up — your observation actually supports something I was guessing.

From what I can tell, Smartsheet:

Sorts values alphabetically by default.
Then, if a dropdown list is defined, reorders based on that list for display.
But cell history or filters may still reflect the original alphabetical sort.

That’s likely why filtering by “Utility, Telecom, Waste” doesn’t behave as expected — it doesn’t match the internal representation Smartsheet is using.

As for the cell history not updating: if the new formula result is the same as the current value, Smartsheet won’t log a new change — but the yellow highlight still appears due to sheet activity.

Ami Veltrie

@jmyzk_cloudsmart_jp Thank you for that additional information.

I reordered the drop down and the formula. This formula solved the problem:

jmyzk_cloudsmart_jp

@Ami Veltrie

I am glad to hear that you have finally solved the problem!😅

I learned a lot myself.

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.