Update a Column on Employee Sheet from another Answer Form sheet

SEBERCAW · 07/21/22

I am very new to Smartsheet.

I created a form that has Employee Names and columns of different types of Safety Credentials.

I then created a new "Answer" sheet that is linked to a form.

When the "Answer" sheet is updated with the date of a credential, I want to do two things.

Update the corresponding column in the Employee sheet if the user (name of employee exists)
If the employee does not exist, transfer the entire row to the Employee sheet

saurabhmaheshwari · 07/22/22

@SEBERCAWWith automation workflows, you should be able to achieve this. Can you please share more details?

Is form created on top of Employee sheet or Answer sheet?
How are you making updates to Answer sheet? Manual record addition?
Can you share columns and structure of both sheets?

SEBERCAW · 07/22/22

The attached form is filled out by an Articulated Driver. He or she probably would only put the date on one of the Certificates on the form such as Erosion 1 or First Aid.

The answers go to the Articulated Drivers Form Answers.

What I need is if the Name on the Articulated Drivers Form Answers matches a Name in the ARTICULATED DRIVERS TRACKER sheet, then the date that the user put on the form is transferred from the Articulated Drivers Form Answer sheet (cell of appropriate certificate) to the ARTICULATED DRIVERS TRACKER sheet (cell of appropriate certificate).

If the Name on the Articulated Drivers Form Answers sheet (gathered from the Form) is unique, then the entire row is copied to the ARTICULATED DRIVERS TRACKERS sheet.

Thanks in advance for your help.

Articulated Drivers Tracker Form Answers Sheet.JPG

che.rabajante · 07/30/22

Hello@SEBERCAW

For names exists on the tracker you may use index collect function to update values

For names not listed / unique you may use Automation workflow to copy row/values

Hope this helps

SEBERCAW · 08/04/22

I found the correct formula, but if there is no date it has an #INVALID VALUE. I tried fixing it with IFERROR, but that does not work. Everything works except when I add the IFERROR and the opening and closing parentheses.

=IFERROR(INDEX(COLLECT({EEO/HARRASSEMENT}, {NAME}, NAME@row, {EEO/HARRASSEMENT}, <>""), 1))

Hollie Green · 08/04/22

You have to tell it what to put when there is an error

=IFERROR(INDEX(COLLECT({EEO/HARRASSEMENT}, {NAME}, NAME@row, {EEO/HARRASSEMENT}, <>""), 1),"")

If the rest of your formula is correct the addition I added should make the if error work and have a blank cell if it is not a date

SEBERCAW · 08/05/22

I found the correct formula, but if there is no date it has an #INVALID VALUE. I tried fixing it with IFERROR, but that does not work. Everything works except when I add the IFERROR and the opening and closing parentheses.

=IFERROR(INDEX(COLLECT({EEO/HARRASSEMENT}, {NAME}, NAME@row, {EEO/HARRASSEMENT}, <>""), 1))

SEBERCAW · 08/05/22

非常感谢! !

Update a Column on Employee Sheet from another Answer Form sheet

Answers

Help Article Resources

Categories