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

Opening this back up as well because I would also like to delete/move a row from the final sheet and not mess up the formulas.

I'm getting into a bit of a circular challenge.

Sheet A - Drop down is selected and whole row is copied to helper sheet

Helper Sheet - Has a column with an Auto # column that advances as the rows are added

Sheet B - base information all will be pulled from Helper sheet including the Primary Column, which is the title.

I have set up references for all of the columns that I need. We'll start with the Title (Primary Column).

Sheet B Columns: Title, Matching Row ID

This works. However, if I manually type the Row ID or use a formula [Matching Row ID]1 + 1, then, when we are done with a row and want to archive it, it breaks the remainder of the columns.

So then, I added an Auto ID column to try and fill the Matching Row ID, but you have to have data to get it to make an Auto #, and this data is dependent on a helper sheet.

So, then, I tried to make something that would create a number that isn't an Auto ID but I did call it that as the column name [Auto ID], and it is either #Blocked or #circular reference since the Title/Primary is an Index/Match formula:
=COUNTIFS(Title$1:Title@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))
=MATCH([Title]@row), [Title]:[Title], 0)
=MATCH([Auto ID]@row, [Auto ID]:[Auto ID], 0)

So, how can I create a formula that will advance the Row ID so that I can use it as the unique identifier attached to an autonumber in the Helper Sheet and move rows to archive when we are done with them?

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.