Need to pull data from row above for formula without using actual row number with


I need to pull data from the row above without having to redo the lower row cell formulas for row #'s every time a new row is added.

Example: From the image above, If I add a row between rows 3 and 4, I need the new row 4 to capture the Last Show Date Engagement City to read the row 3 value of Sun 08-Aug-2021 AND the new row 6 (formerly row 5) Last Show Origin to read the new row 5 value for the Last Show Date Engagement City.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I suggest inserting two additional columns.

    An auto number column (called "Auto") with no special formatting needed.

    And a text/number column (called "Row") with the following column formula:

    =MATCH(Auto@row, Auto:Auto, 0))

    Finally the column formula to pull the date from the row above in the other column would be:

    =IFERROR(INDEX([Last Show Date Engagement City]:[Last Show Date Engagement City], Row@row - 1), "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!