Using the status of the Row Action Indicator (Locked vs Unlocked)

Bob Wiedenbeck

Can the Row Status (Locked vs Unlocked) be used in a cell formula with @row and IF functions?

I want to populate a Card View sorting field/cell with titles/headings using a column formula but I don't want the locked rows to be included in the auto-population of titles/headings since those will never need to be 'actioned'.

My current formula is:

=IF(Status@row <> "Complete", IF([Target Date]@row < TODAY(), "Critical, Overdue", IF([Target Date]@row < TODAY(5), "Active, Due in <1 week", IF([Target Date]@row < TODAY(12), "Pending, Due in <2 weeks", "Future, Due in >2 weeks"))), "Complete, Past History")

If I could add something like: IF([Row Action Indicator]@row="1","",

in front of the first IF in my current formula and close out the false statement section with an ")" at the very end, it should evaluate whether the Row is Locked and return a blank cell, or evaluate the rest of the content to determine what to populate the cell with when the lock status is "0" or unlocked.

I want all 'locked' rows to be "" (blank) in the CardView Sort cell. In my example above, only the R-003, R-004, R-005 and R-001 rows should have 'values' in the CardView Sort column... "Critical, Overdue" for R-003, -004, & -005; and "Complete Past History" for R-001.

Bob Wiedenbeck

Kelly Moore

Hey @Bob Wiedenbeck

A workaround for your Lock/Unlock Row status as part of a formula is to add an additional helper column, like a checkbox, and populate it as you Lock/Unlock rows. You can THEN use the new helper column in your IF column.

For example, your automation could look something like this. I wasn't clear if you ever unlocked your 'locked' rows, so skip the Clear Value automation and/or Unlock Rows if you don't use it.

In my example, the [Change Cell Value] column is the helper column to use in an IF statement. I have another simple checkbox column for my lock/unlock trigger but in your case this would be whatever you already have built. You would just add the Change Cell Value step to the end of your automation.

In this example, you would add IF([Change Cell Value]@row = 1,"" to the beginning of your IF statements.

Would this work for you?

Kelly

Bob Wiedenbeck

Thanks Kelly, I'll give it a try... These locked rows are for static "header" content in the sheet, not active rows that get their status changed from locked to unlocked or from unlocked to locked. As such, they just don't fit into the whole idea of action cards that need to be sorted. I'll have to see if the Automation will override the column formula driven value which populates the CardView Sort cells.

Edit: All of the columns that have column-based formulas are not listed in the set of cells that can be changed... So this work-around method doesn't work.

Bob Wiedenbeck

I was able to add a column (which I have to populate), identifying whether the row has been locked or not (mirroring the Smartsheet Locked Row Action Indicator info). I can then add this into the Column Formula for my CardView Sort field as another IF statement...

This accomplishes what I wanted; but I'm disappointed that I have to create something redundant (and which has to be manually filled) only to report on something else that is embedded and associated with every row (which already has a specific data 'value' to Smartsheet), but can't be accessed/referenced by users.

Kelly Moore

Hey @Bob Wiedenbeck

It seems odd that if you have automation to lock/unlock rows that you have to manually add anything to a different column. The change cell value/clear cell value should be able to populate a column based on some trigger if that same trigger is also being used for other automation.

Kelly

Bob Wiedenbeck

Sorry Kelly if I've created confusion... I have not automated locking/unlocking rows. And as I stated above, Automation will not allow me to change values in any cells/columns which have column based formulas driving their content. Those columns are simply not in the list of values that I can choose from. I assume it is because they are driven by formulae and those formulae cannot be overridden by Automation.

All I wanted to do was reference the status of lock/unlock within a nested IF formula in a column-based formula. I have found that an Enhancement Request was written in 2022 to allow users to access this type of content (comments on row, attachments on row, locked/unlocked row, open update request on row, etc.), within formulas. I've added my vote (which unfortunately only makes 4 votes now) and added commentary as to the many benefits I can see would come from that feature addition.