Cloning a sheet with Index/Match

Options

I am looking to transfer 90% of data from a source sheet to a destination sheet. Currently both sheets are empty. I plan to populate the source sheet by use of a form and hope the destination sheet populates with INDEX/MATCH. The problem I am running into is how to populate the primary key for the destination sheet, to drive the rest of the INDEX/MATCH calculations.

In testing, I received the #circular reference error for the PK on the destination sheet and a #blocked error for the one other index match calculation which references the PK.

If I decide to add the PK to both sheets (just 1-200 etc), then I think I solve those issues.... but then, when I perform the data entry with the form, how can I ensure the entries are recorded on each row that already has a PK on the source sheet? I did a test on this and it populated on the next available row, and did not copy to the destination sheet.

Answers

  • Timrapuano
    Options

    just for clarity of the result of my test using the form for data entry.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @TimRapuano

    I hope you're well and safe!

    You'd use the Autonumber System Column to get the ID automatically. Then you can add that number manually to the destination sheet or use the INDEX function in a formula to do it automatically.

    Would any of those options work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Timrapuano
    Options

    Hi Andree, thank you for the suggestion. As I understand the PK cannot be autogenerated, but I can create an auto generated column which can create the PK by applying this formula: =[Sys Gen Column Name]@row

    I inserted it and converted it to a column formula. Before I knew it, when the page refreshed it populated 50 rows of the autonumber and the PK. When I ran the form it added information to line 51.

    I also used the index function to copy over data to the destination sheet, but the formulas on the destination sheet were on row 1, so nothing was copied over, not even the PK.

    Is there a way to add form data to a row if the row already has the autonumber generated?

  • Timrapuano
    Options

    Andree, transferring the PK with MATCH runs into the problem of the circular reference error. In my calculation below, the master project number is the PK on the source sheet and the client study number is the PK on the destination sheet.

    =INDEX({Master Project Number}, MATCH([Client Study Number]@row, {Master Project Number}, 0))

    If the destination row is empty, there is no reference to copy over all of the other column data for that row. Smartsheet needs to match the PK in both sheets before it can copy information over. Without a match, it has no reference.

    Using the autonumber mentioned earlier works great, if there is no form being using. I think moving forward I will manually (copy and paste) the PK to the destination sheet as you also suggested.


    Thanks!

    Tim

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!