Changing cell value based on another cell

Zsolt

Hi all,

After tackling several programming languages I have a seemingly simple task that I got stuck with in Smartsheet. Let's say you have Score and Max Score columns in a row. Score is changed by automation. It can go up and down. I want to store the value of Score in Max Score but only if Score is larger than Max Score. (which is the definition of max score anyway).

How do you do this? IF could determine the condition but if it is false, there's no way to say "keep what you have currently." Automation can change the cell value but I don't see how I can set a cell value based on another cell value and not an absolute value I type in.

JCluff

@Zsolt

How are you planning on changing the score by automation? You can copy values to another sheet, but it just adds a new row to the second sheet. If you have that part solved through an add-in, then you can do the following:

• add a checkbox column with formula =if([New Score]@row>{Max Score Reference},1)
• Create a second sheet as an exact copy of first sheet + 1 new text/number column
• On first sheet, create automation that if checkbox is checked, copy row to new sheet
• On second sheet in the new column, use =MAX(New Score:New Score) formula to determine max score.

Zsolt

Thanks, @JCluff !

On one hand, I'm amazed how much smartsheet can do, an another I'm baffled why simple things must take so much extra work. I'm always tempted to just write a couple of lines of Python code and it's all done through the SDK.

Tomasz Giba

Hi @Zsolt

I was able to create "some" solution for it, but I have a few cons that I doubt can be currently overcome:

After reaching a new max the sheet has to be saved manually and to see this new max in the Max Score column this sheet has to be refreshed. These lags are probably not possible to avoid due to automatically copying a row to another sheet and recalculating back new Max to the first sheet.

So if you still looking for this solution in smartsheet and your needs accept these lags and some manual saving/refreshing then let me know, and I will describe it.

Cheers!