How to delete/move a row from the final sheet and not mess up the formulas.
Best 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 502. 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
-
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 sheetHelper 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?
-
Any thoughts on this issue?
-
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?
-
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 502. 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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!