Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Absolute references in an Index Match formula

    ✭✭✭✭
    in Formulas and Functions

    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:

    image-82d677501fd19-a79e.png

    Intake Sheet:

    image-e5a6fc008d9cd-cf7f.png

    Cross Sheet References:

    image-8d055adda0085-25f2.png

    image-028129a288b18-e154.png
    image.png

    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.

    · Share on FacebookShare on Twitter

    Best Answer

    • Community Champion
      Answer ✓

      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

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      @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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions