How do I have SSheet automatically update a cell based on form submission.

Teresa Williams

Hope someone can help with this one! I have an form set up where staff enter what equipment was used for each shift. The form has an option to select "no changes from previous shift".

How can I have that cell copy the information that was entered on the previous form submission?

Current Workflow: employee pulls up form and selects "no change from previous shift" and hits "send". I then go in to spreadsheet (gridview) and copy the information they entered the previous day and paste it into the new day.

Ideal Workflow: employee pulls up form and selects "no change from previous shift" and hits "send". Smartsheet automatically updates cell with information from previous date. (so I don't have to manually do this)

Thank you!

Mark Cronk

Hi Teresa,

I don't think what you're asking to do is possible. However, there are lots of really creative super users in the Community. Someone may have an idea for you.

Mark

Toufong Vang

Depending on how your sheet is structured, it might be possible. Complex, but possible. You'll likely use MAX(), COLLECT(), INDEX(), IF() functions, and will need to nest them, too.

Assuming that you're using one sheet, you'll likely have many entries for each employee. The challenge, then, is to identify only the entries for the employee whose record is being worked on, and then retrieve the needed information from the most recent entry.

If your sheet is structured like the example above, then the approach might be...

1) = IF(No_Chang@row = 1,<<CODE_BLOCK>>)

2) The code block needs to find the most recent shift for Bill_321. Most Recent Shift...

MAX(COLLECT(Shift_Date:Shift_Date, Staff_ID:Staff_ID, =Staff_ID@row), Shift_Date:Shift_Date,<Shift_Date@row)

3) Then use the result from Step 2 to identify the details from Equipment_Used.

INDEX(COLLECT(Equipment_Used:Equipment_Used, Staff_ID:Staff_ID, Staff_ID=Staff_ID@row, Shift_Date:Shift_Date, = <<Most Recent Shift EXPRESSION from Step 2>>),1)

4) This code block (from Step 3) would be inserted into the code from Step 1.

This is untested--it's just to present one approach.

Hope this helped.

Good luck!