Formula to pull data from one sheet into a different sheet.

Options

Hello Smartsheets,

I am building a sheet that will track an employees attendance of our monthly trainings. Sheet #1 is the raw data, Sheet #2 contains all of the formulas and combines all raw data in a legible way. See photos below.

Sheet #1

This sheet contains all raw data received from the attendees of each monthly training. Each column is a new month that I will manually enter into this raw data sheet.

Sheet #2

Sheet #2 will have formulas connected to each column specifying which employee completed their monthly training. For example: if user1 completed their September 2021 training, "completed" would show up in that cell.


Sheet #2 needs to pull who completed the training within each month. I have used an INDEX,MATCH formula and it does not come back with accurate results.


Please let me know if you have any ideas!

Thanks

Answers

  • Krissia B.
    Krissia B. Moderator
    Options

    Hello @Ruby Wood

    Thank you for your post! Upon reviewing what you are requesting & testing this, the following may work to what you are trying to accomplish.

    I used the formulas below.

    • If you just want the word "Completed"
    • =IF(HAS({September 2021}, Alias@row), "Completed")
    • If you want the word "Completed" as well as the Associated Name
    • =IF(HAS({September 2021}, Alias@row), "Completed" + ": " + [Associate Name]@row)


    Screenshots below.

    Sheet 1

    Sheet 2


    Hope this helps!

    Cheers~

    Krissia

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!