Help with Automation between (2 sheets) - Index Formula

CatrinaF.ONE

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

1. 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.
2. The information from the Source sheet is pulled from a form.
3. 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))

MVP OPS

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,