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 autofilled 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 timesaving if I could just have it automatically fill out the correct cells on sheet 1
Answers

@SAnkney Does this work for you?
Set up crosssheet references on Sheet 1 for Project Name, EE, and PM columns on Sheet 2
Names of crosssheet 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))))

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

@SAnkney In that case you can either set up the extra crosssheet 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)

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

Added 2 new Type options to Sheet 1
Added 2 new columns to sheet 2: Facilities & Quality
The two new crosssheet 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

@jessica.smith Yes, I got it, thank you so much for the help. 😄
Help Article Resources
Categories
Check out the Formula Handbook template!