Changing a cell Value to different cell value when a 3rd cell changes

Jtjpilot18

I would like the last row on the right to change to the cell that says "framing" ONLY when the first cell shows "In Progress." Is this possible? I've been trying this for a few hours now…

jmyzk_cloudsmart_jp

Hi @Jtjpilot18

In short, are you asking, "When the last filled row's leftmost cell says 'In Progress,' update the rightmost cell to say 'Framing.'?

As for the 'last row' condition, if you can use the system column, auto number, or Row ID, in this example, and assuming you could move the rows up and down, first create a helper column Row with the following formula;

=MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Then, you can determine if a row is the last row by this formula: (If you do not plan to change the position of rows, you can substitute Row with Row ID)

MAX(Row:Row) = Row@row

Then, you can combine this with the second condition that 'the row's left most cell says In Progress' with a formula like this;

=IF(AND(MAX(Row:Row) = Row@row, [Task Status]@row = "In Progress"), "Framing")

I could not find a way to determine if a cell is the leftmost or rightmost, so I set the column manually.

https://app.smartsheet.com/b/publish?EQBCT=83bf34f61ef744a5ade74233552e2978 (This published demo sheet is editable, so you can change the Task Status or add rows to check how the formula works.)