Help with Automation between (2 sheets) - Index Formula
There are currently two sheets - (a) Source Information Sheet and (b) a destination sheet.
I am attempting to use a formula within the destination sheet to pull information into the source sheet.
The formula that was used to populate the original sheet (which is an Index Formula) works in all cells but 2.
I cannot use the cell link feature because it locks into the exact cell that is selected and I need things to populate with new updates. (So always the row associated with the ID number.)
Additional Infromation
- I am not the original owner of the sheet and I have no access to them, so I do not know the history of why things are the way they are.
- The information from the Source sheet is pulled from a form.
- There were additional fields that were added into the Source Sheet once we added additional information from the form.
Picture for the formula that does not work -
Source Sheet
Always pull from the bottom row (highlighted) and include the Scope Statement.
Destination Sheet
Current Formula that does not work =INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Scope Statement]$1, {Portfolio Summary Header Row}, 0))
Formula that works (listed in the another field on the destination sheet)
=INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Project Manager]$1, {Portfolio Summary Header Row}, 0))
Answers
-
Hello CatrinaF.ONE,
This sheet looks more complicated than it needs to be. Possibly a control center blueprint that is no longer being used?
I'm not sure where {Portfolio Summary Data} is coming from, as I dont see a column named that on your screenshots.
Before doing the following steps, make a copy of the sheets you're about to modify by "Saving as new". Then you can test away.
For simplicity and clean up you could do the following:
Remove row 1 in each sheet - those rows are the same as the column names
Source sheet: On your project intake (source sheet), you're going to use the Project ID as your lookup for all columns in your destination sheet.
Destination sheet:
In each column write a simple index match formula to pull the data in from each column in your source sheet using the project id as the match criteria.
You will need to have the project ID available on your destination sheet.
=index({Scope Statement from source sheet and all other columns}, match(Project ID@row, {Project ID Column from Source sheet},0))
Once you have added this formula to each column you could then save as new on the sheet and then just input the project ID to pull in the data from your source sheet for each project.
Good luck and hope this helps,
best,
Brad
www.MVPOPS.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!