Using formulas to copy, or transfer data between cells.

austinurbanSeagate

Hello community!

    I am looking for a seemingly easy solution which I can't find. While the workflow automation helps in many ways, the "actions" available seem to be greatly lacking. For example in google sheets + scripts + triggers, I can have a cell update based on a formula from a time driven event (whether external or from a cell's given date). While the time driven events functionality is already there in smartsheet (sending out update requests etc), I can't find how to have it update a cell without user input. I have attempted many work-arounds with no success.

    From my employer's perspective we can't link outside resource programs like Zapier to enable features like this, and I want to figure out how to within smartsheet itself. Overall, I think the functionality may not be a available to users; so any answer to the following questions will help me get further in exploring work-arounds:

-Can I create a dynamic cell link between two sheets, i.e. once the last column gets filled in a row, the linked cells transition down one row. (almost treating the parent sheet as a web-form and sheet2 is the response collection)

-Can I have a cell copy itself to another cell (w/o user input) through a function based process (not "linking" the cells' values)

-Are there more workflow actions accessible which aren't presented in the "workflow actions" section?

-Can I code my own function if the functionality I need is not available to current users?

-Can I have a cell function update itself on a time / edit basis while referencing its original value /data and not creating an infinite loop (having a weekly occurring due date update itself to a week out after a completion box has been checked, running for infinite weeks until specified).

My project is a maintenance system, where I have a master sheet with one row for each maintenance item which I want to have update and overwrite rows to only contain the most recent maintenance item. Once the maintenance item is check complete, I want that maintenance service logged, and the row to update to be ready for the next time the maintenance item needs to be completed. From there each maintenance item has its own separate sheet with a maintenance and notes history (where I want the logging to be stored). 

Andrée Starå

Hi Austin,

Please see my answers below.

-Can I create a dynamic cell link between two sheets, i.e. once the last column gets filled in a row, the linked cells transition down one row. (almost treating the parent sheet as a web-form and sheet2 is the response collection)

Unfortunately, it's not possible at the moment, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

-Can I have a cell copy itself to another cell (w/o user input) through a function based process (not "linking" the cells' values)

You could use a formula. Is that an option?

-Are there more workflow actions accessible which aren't presented in the "workflow actions" section?

No

-Can I code my own function if the functionality I need is not available to current users?

Can you clarify what that function could be?

-Can I have a cell function update itself on a time / edit basis while referencing its original value /data and not creating an infinite loop (having a weekly occurring due date update itself to a week out after a completion box has been checked, running for infinite weeks until specified).

No

My project is a maintenance system, where I have a master sheet with one row for each maintenance item which I want to have update and overwrite rows to only contain the most recent maintenance item. Once the maintenance item is check complete, I want that maintenance service logged, and the row to update to be ready for the next time the maintenance item needs to be completed. From there each maintenance item has its own separate sheet with a maintenance and notes history (where I want the logging to be stored). 

I think we can come very close to what you seem to need with cross-sheet formulas and VLOOKUP or an INDEX/MATCH combination.

To add to my answers above. The Smartsheet API might be an option for almost all of them.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

austinurbanSeagate

This was a fantastic answer! Looking into the API now, hadn't heard of it until your response. 

Andrée Starå

Excellent!

Happy to help!

More info about the API: http://developers.smartsheet.com/