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

System

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

paige_pj

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.

paige_pj

Any thoughts on this issue?

paige_pj

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?

https://community.smartsheet.com/discussion/105584/how-can-i-set-a-row-id-column-that-does-not-change-when-you-move-add-or-delete-rows

paige_pj

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.