Auto Populate Cell Reference by field names

dpm1028

This is going to be hard to explain, but I'm hoping I can do it.

I have a sheet named "ProjectSheet". Under Task Column I have multiple "Applications". Under each Application is a Project Stage (Data Gathering, Build, Post Go Live). Under each Stage are two roles (IC, IE) (Top - image)

I have a second "Team_Dates" sheet that only has Application and Role. (Below).

My goal is to populate the Resource column in the "ProjectSheet" for all IC's and IE's based on the "Application" name and the role "IC" from the "Teams_Dates" sheet. I know I can to build a cell reference for every single resource in the ProjectSheet, but I am hoping that there is a formula that I can use in all the columns that refer to the Application and Role from TeamDates and auto populates the ProjectSheet.

I hope that made sense!!!!

Hunter Taylor

Hi dpm1028,

I've made a step by step process that should hopefully get you the outcome your looking for.

Steps

1. Understand the Logic:
   • For each IC or IE in the "Task" column (under each "Application"), lookup the matching "Application" and "Role" in the Team_Dates sheet and return the corresponding Resource.
2. Set Up the Sheets:
   • Ensure the Team_Dates sheet has these columns:
     • Application (Application Name)
     • Role (IC/IE)
     • Resource (Assigned Resource)
   • Ensure the ProjectSheet has a Task column where the application names and roles (IC/IE) are nested as per the structure in your images.
3. Use Cross-Sheet References:
   • Create a cross-sheet reference for the Team_Dates sheet:
     • In the ProjectSheet, click on any cell and start typing a formula that references the Team_Dates sheet.
     • When prompted, create a cross-sheet reference to the Application, Role, and Resource columns in the Team_Dates sheet. Name the reference something like:
       • TeamDates_App
       • TeamDates_Role
       • TeamDates_Resource
4. Write The Formula:
   • Use the INDEX and MATCH functions to dynamically populate the Resource column in the ProjectSheet.
   Here’s the formula you can use in the Resource column:

=INDEX({TeamDates_Resource}, MATCH([Parent Application]@row + [Role]@row, {TeamDates_App} + {TeamDates_Role}, 0))

Explanation:

• INDEX({TeamDates_Resource}, ...) fetches the corresponding value from the Resource column in the Team_Dates sheet.
• MATCH(...) finds the row in the Team_Dates sheet where the Application and Role match the current row's Application and Role in the ProjectSheet.
• [Parent Application]@row assumes the "Application" name is stored at a higher (parent) level in the hierarchy. Adjust based on your exact setup.
• [Role]@row is the "IC" or "IE" value.

5. Drag the Formula Down:

1. Once the formula is written for one row, you can drag it down or use Auto-Fill to apply it to all relevant rows in the Resource column.

6. Test the Formula:

1. Verify the correct Resource is populated based on the Team_Dates data.

I hope this helps. If you have any other questions or need more help let me know.