Formula that updates a cell in another column

Does anyone knowif there is a way to have a formula in one cell update a cell in another column? Currently I have a formula for a check box column (Task Complete Column) that checks the % Complete and if the task is 100% complete the check box is checked automatically.

eg: (=if ([% Complete]4,1,1,0) This updates the check box for row 4 "Done" column.

If the user manually clicks the "Done" check box, I would like to update the % Complete to 100%.

If you place a formula in the % Complete cell and someone enters 25%, the formula is wiped out.

I thought I could have a dummy, hidden column that checks the Done check box and if it is checked I would update the % Complete from the dummy column. I have not been able to find a way to do this. I can do this in the dummy column but, it doesn't update the % Complete and the task doesn't show as complete. Any suggestions or am I making this too complicated? Right now I just told the users to update the % Complete and all is well. I just keep thinking that there should be a way to do this. I have asked Support but, they have not responded with a solution yet.

Thanks for any suggestions...

jerry

Find more posts tagged with

Formulas

Legal

Project Management

Comments

Richard Rymill SBP

Jerry, I suspect you are focusing too hard on a complex solution when reviewing the workflow to find a more user friendly way to get what you want ,can often be a simple revelation and we love solutions that motivate workflow users better.

That said, if you do the above AND can still benefit from Automation then our team is always happy to tackle a challenging formula question?

Regards

RichardR

Jerry Tutt

Richard, your point is well taken. My initial response to the user was just make it 100% complete and it will automatically check the box. It seems so simple that I couldn't resist the challenge to automate it. If I find a way to do this I will definately post it.

Thanks,

jerry

J. Craig Williams

Jerry,

Smartsheet does not have macros or scripting functionality. Once a user updates a cell, the previous contents are lost. What you are looking for is something like a "put" function. The API can handle that, along with several third party tools that access the data via the API.

I feel your pain though. The % Complete column can't have a formula (if dependencies are enabled) but it is SO MUCH EASIER to click a check box than to type in 100%.

Any friction leads to dissatisfied users. Or they ignore the % Complete and go back to the binary days of done/not done.

Craig