Trying to autofill an assigned person from a different sheet

I'm trying to get the assigned person with the contact list to autofill based on company position and project name.

So I have Sheet 1, I would like the "Assigned To" to be auto-filled from Sheet 2, by using the Project name and Type on Sheet 1.

Sheet 1

Sheet 2 -

Sheet 2 is always filled out first to pick a group of people to work on the project. So it would be time-saving if I could just have it automatically fill out the correct cells on sheet 1



  • jessica.smith
    jessica.smith ✭✭✭✭✭
    edited 08/29/23

    @SAnkney Does this work for you?

    Set up cross-sheet references on Sheet 1 for Project Name, EE, and PM columns on Sheet 2

    Names of cross-sheet references in formula below: {Sheet2_ProjectName}, {Sheet2_EE}, {Sheet2_PM}

    Use an Index/Match formula nested within an IF formula in the Assigned To column on Sheet 1.

    =IF(Type@row = "EE", INDEX({Sheet2_EE}, MATCH([Project Name]@row, {Sheet2_Project}, 0)), IF(Type@row = "PM", INDEX({Sheet2_PM}, MATCH([Project Name]@row, {Sheet2_Project}, 0))))

  • SAnkney
    SAnkney ✭✭✭


    Yes, this worked, but I forgot to mention I have 10 different columns it has to choose from on sheet 2 🤦

    Thank you so much for the help. 😊

  • jessica.smith
    jessica.smith ✭✭✭✭✭

    @SAnkney In that case you can either set up the extra cross-sheet references and add further IF statements to the equation or modify Sheet 2 so that each row corresponds to a single assignment for a specific position on a project.

    You would have more records on Sheet 2, but you'd reduce the complexity of formula and sheet connections. If you went with method 2, your Index/Match formula would become an Index/Collect formula instead.

    Example: =INDEX(COLLECT({Sheet2_PM}, {Sheet2_Project}, [Project Name]@row, {Sheet2_Position}, Type@row), 1)

  • SAnkney
    SAnkney ✭✭✭

    @jessica.smith Sadly I think keeping it as each column for each Position would be better for when the teams are decided.

    How would I go about adding more IF statements to the equations?

    I think just a small sample of the equation on how to nest them will help me.

    Thank you for the help.

  • jessica.smith
    jessica.smith ✭✭✭✭✭


    Added 2 new Type options to Sheet 1

    Added 2 new columns to sheet 2: Facilities & Quality

    The two new cross-sheet references are: {Sheet2_Facilities} and {Sheet2_QA}

    Updated formula:

    =IF(Type@row = "EE", INDEX({Sheet2_EE}, MATCH([Project Name]@row, {Sheet2_Project}, 0)), IF(Type@row = "PM", INDEX({Sheet2_PM}, MATCH([Project Name]@row, {Sheet2_Project}, 0)), IF(Type@row = "Facilities", INDEX({Sheet2_Facilities}, MATCH([Project Name]@row, {Sheet2_Project}, 0)), IF(Type@row = "Quality", INDEX({Sheet2_QA}, MATCH([Project Name]@row, {Sheet2_Project}, 0))))))

    Conceptually, to build an IF Statment you write =IF(Logical Expression, Value if True, Value if False). In this formula, the Index/Match formula is the True Value and the next IF statement is the False value

  • SAnkney
    SAnkney ✭✭✭

    @jessica.smith Yes, I got it, thank you so much for the help. 😄

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!