Update a Column on Employee Sheet from another Answer Form sheet

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.


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

Answers

  • saurabhmaheshwari
    edited 07/22/22

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

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

    Regards,

    Saurabh Maheshwari

    Smartsheet Superstar

    Portfolio Lead, TCS

  • 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.




  • che.rabajante
    che.rabajante ✭✭✭✭✭

    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

  • 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
    Hollie Green ✭✭✭✭✭✭

    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

  • 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))

  • Thanks so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!