Cloning a sheet with Index/Match

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.

Find more posts tagged with

Accepted answers

All comments

Timrapuano

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

Andrée Starå

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!

Timrapuano

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

https://community.smartsheet.com/discussion/comment/379501#Comment_379501

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.