Trying to autofill an assigned person from a different sheet

SAnkney

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

@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

@jessica.smith

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

@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

@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

@SAnkney

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

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