Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    Employee
    in Formulas and Functions
    This discussion was created from comments split from: Copy one column to new sheet as rows are added.
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭
      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.

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭
      edited 08/19/24

      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

      image.png

      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?

      · Share on FacebookShare on Twitter
    • ✭✭

      Any thoughts on this issue?

      · Share on FacebookShare on Twitter
    • ✭✭
      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?

      How can I set a Row ID column that does not change when you move, add or delete rows

      mclawrence_123
      · Share on FacebookShare on Twitter
    • ✭✭
      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.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions