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
Answers
-
@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))))
-
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 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)
-
@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 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
-
@jessica.smith Yes, I got it, thank you so much for the help. 😄
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!