How to delete/move a row from the final sheet and not mess up the formulas.

This discussion was created from comments split from: Copy one column to new sheet as rows are added.

Best Answer

  • paige_pj
    paige_pj ✭✭
    Answer ✓

    Last post…still interested in getting help with this. However, as a work around, I:
    1. Created a Helper Column that I put - in each cell down to row 50

    2. That allowed me to create a Auto Number Column.

    3. Made the 'Matching Row ID': =MATCH([Auto ID]@row, [Auto ID]:[Auto ID], 0)

    4. Then, had to make the Title Column: =IFERROR(INDEX(COLLECT({Title_HelperSheet}, {RowID_HelperSheet}, VALUE([Matching Row ID]@row)), 1), " ")

    Now, hoping that doesn't somehow break for the other columns I'm pulling from the Helper Sheet. Would love to be able to do this without the #1 Helper Column because that is a manual helper that has to have data, which means making sure that we remember to increase the - down if needed.

Answers

  • Any thoughts on this issue?

  • paige_pj
    paige_pj ✭✭
    edited 08/21/24

    Nevermind…it works as a formula, but it is a circular reference to the fact that the Title row relies on the Matching Row ID. So, it doesn't actually all work. :(.

    It seems like I have reached a limit for SmartSheet.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Actually, I tried again, and I was able to make this work:

    =COUNTIFS(Title$1:Title@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

    However, I saw in this that to make this a column formula, you have to replace the @cell with @row, and that doesn't work. But, it works with the @cell. Is there another change I have to make to make this work for a column formula?

  • paige_pj
    paige_pj ✭✭
    Answer ✓

    Last post…still interested in getting help with this. However, as a work around, I:
    1. Created a Helper Column that I put - in each cell down to row 50

    2. That allowed me to create a Auto Number Column.

    3. Made the 'Matching Row ID': =MATCH([Auto ID]@row, [Auto ID]:[Auto ID], 0)

    4. Then, had to make the Title Column: =IFERROR(INDEX(COLLECT({Title_HelperSheet}, {RowID_HelperSheet}, VALUE([Matching Row ID]@row)), 1), " ")

    Now, hoping that doesn't somehow break for the other columns I'm pulling from the Helper Sheet. Would love to be able to do this without the #1 Helper Column because that is a manual helper that has to have data, which means making sure that we remember to increase the - down if needed.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!