Index Match to pull data from Project Intake sheet to Project Plan

Hi- I am setting up the PMO Template for my organization. I have the project intake sheet fully populated and now I am creating my project folders... Is there a formula that can be used to pull task "assigned to" info from my project intake sheet into the project plan sheet?

My project plan has 50+ tasks but I want to only use the formula on 4 tasks.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @JIM_AFO

    Depending how your project sheets are set up, you should be able to relay information from your intake sheet to your individual project sheets. Both intake sheet and project sheet need a linking unique identifier - such as the project number- to make a lookup function work. For the Assigned to column - both sheets have to be the same as to whether you are allowing multiple entries. You cannot have one sheet be marked as multiple contacts and the other as single contacts.

    =INDEX({intake sheet Assigned To column}, MATCH([project sheet project number]@row, {intake sheet project number column}, 0))

    remember with a cross sheet reference you must physically create these on your sheet, you cannot simply copy paste this formula into your sheet

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @JIM_AFO

    Depending how your project sheets are set up, you should be able to relay information from your intake sheet to your individual project sheets. Both intake sheet and project sheet need a linking unique identifier - such as the project number- to make a lookup function work. For the Assigned to column - both sheets have to be the same as to whether you are allowing multiple entries. You cannot have one sheet be marked as multiple contacts and the other as single contacts.

    =INDEX({intake sheet Assigned To column}, MATCH([project sheet project number]@row, {intake sheet project number column}, 0))

    remember with a cross sheet reference you must physically create these on your sheet, you cannot simply copy paste this formula into your sheet

    Does this work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!