Absolute references in an Index Match formula

Celia Gust

We have a sheet called Intake Sheet that has a line for each project. Each project has a Project Metadata sheet that pulls information from the Intake sheet by using an Index Match formula with cross sheet references. This was set up for us by a Smartsheet developer and it did work but I moved some columns around in one of the Metadata sheets and now it is pulling the data from 1 column to the right of the intended column.

In the example shown, instead of pulling the Project Overview it is pulling the results from 1 column to the right, Project Sponsor.

Index Match formula from the Project Metadata Project Overview column:

=INDEX({Portfolio Summary Data}, MATCH($[Project Name]@row, {Project Name}, 0), MATCH([Project Overview]$1, {Portfolio Summary Header Row}, 0))

Screenshot of Metadata sheet:

Intake Sheet:

Cross Sheet References:

Is there an absolute reference that is causing it to pull the column to the right? I can't figure out what I'm missing, but it must be something.

Kelly Moore

Hey @Celia Gust

It seems the range of the {Portfolio Summary Header Row} reference might not have updated when the column order was changed . The purpose of the 2nd Match in the formula is to identify the correct column number to select. If you click on the {Portfolio Summary Header Row} within the formula, the option to Edit the Reference will pop up. You can reselect the correct range and update the reference. Begin at the first column, [Project ID]1, and select all the rest of the columns in that row then update. Hopefully that will remedy the problem

Kelly

Kelly Moore

Hey @Celia Gust

It seems the range of the {Portfolio Summary Header Row} reference might not have updated when the column order was changed . The purpose of the 2nd Match in the formula is to identify the correct column number to select. If you click on the {Portfolio Summary Header Row} within the formula, the option to Edit the Reference will pop up. You can reselect the correct range and update the reference. Begin at the first column, [Project ID]1, and select all the rest of the columns in that row then update. Hopefully that will remedy the problem

Kelly

Celia Gust

@Kelly Moore

This worked!!! It was the Portfolio Summary Data that wasn't updated correctly, but redoing the column selection did the trick. I also had to update the reference in each Metadata file, but that was no biggy since I'm only just starting to use this system. I really appreciate your prompt response. I was quite worried that I had messed the whole thing up.

Thanks again! Celia