Need advice on moving auto numbered data to another sheet with other auto numbered data.

Options

Hello, I'm wondering if anyone has a work around for this scenario. I have two sheets:

Sheet 1 - New Project Requests - includes the auto numbered column NPR ID#

Sheet 2- Active Projects - includes the auto numbered column PRJ ID#

When an NPR is approved for work I want to copy the row data from Sheet 1 to Sheet 2 but each time I do this it forces the NPR ID# into the PRJ ID# field. In Sheet 2 I only want to keep the NPR ID# for traceability, I do not want it to replace the auto numbered PRJ ID#. Any ideas?

Tags:

Answers

  • eliweitz
    eliweitz ✭✭✭✭✭
    edited 06/11/24
    Options

    Hey @tmcpherson! Hope all is well! Smartsheet currently only supports one autonumbered row per sheet, per my current knowledge. You might actually want to create a new column, which pulls the value from the NPR ID# column into a text value, then move it over to the new sheet, so it will autonumber with a new PRJ ID#, and keep the existing NPR ID# as well.

  • tmcpherson
    Options

    Thanks @eliweitz - the proposed work around you're suggesting sounds interesting - can you elaborate on this?

    I think copying a row over to another sheet is restricted to all or nothing, so if my Sheet 1 includes the original auto numbered NPR ID# then it doesn't seem possible to stop that from auto-populating to the PRJ ID # field in Sheet 2 after the copy. This happens even though the two columns have different names. Frustratingly, I can't seem to delete that auto populated field value from Sheet 2 either.

  • eliweitz
    eliweitz ✭✭✭✭✭
    edited 06/12/24
    Options

    Hey! Theoretically, my pitch is to do a direct cell reference to the AutoNumber cell in the row, which would convert it to a text value, kind of like this:

    By doing a cell reference instead, I now have a new text value copy of the Autonumber's value. Theoretically, hiding the autonumber, and selecting all other values on the row (SHIFT+Click) should allow you to select most of the content of the row, and could help with not copying an autonumber (although it would need to be perfectly aligned with the destination's columns. In the end, my solution only allows for two ID# columns still in the destination form, as there isn't a way to skip any autonumbering.

  • tmcpherson
    Options

    Thanks @eliweitz this sounds like it will work! I'll give it a try!