how to subtract cells from different sheets with the same id?

In my sheet A I have an ID and a Modify column just like sheet B.
I want to subtract the Modify of sheet B with the one of sheet A that have the same ID.

can you help me with a formula?

Best Answer

  • Julie Fortney
    Julie Fortney Overachievers
    Answer ✓

    You can use a cross-sheet formula to pull in the modify value from the other sheet, then subtract them.

    Assuming you want to calculate Sheet A Modify - Sheet B Modify, you could create the following on Sheet B:

    The Sheet A column formula is:

    =INDEX({test - Sheet A - Modify}, MATCH(ID@row, {test - Sheet A - ID}, 0))

    In narrative form, we're telling Smartsheet to pull the Sheet A Modify value from the Sheet A row that has an ID matching the ID of this row on Sheet B.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!