Archiving rows with formulas that reference other sheets

I've build a pretty large Smartsheet for tracking data yearly contracts for each of my 60-70 clients on therapy caseload with formulas in each row that reference other sheets and pull numbers from daily log forms.

I need a simple process for archiving a row when the year contract is complete. I want to save a snapshot of the data from that year. The issue I run into is that when I went to create a copy of the caseload sheet it asked me to include the formulas and sheet references in the copy and I clicked no cause I just want the numbers and don't want to continue to pull the new data numbers in my archive sheet. I what it to be saved with the current numbers as a fixed record for that year. But when I copied the sheet and excluded the sheet reference it dumps the data in most of the cells in the row and just returns errors.

Is there any way to dump the sheet reference just keep the numbers when archiving? like a copy and paste that just takes the numbers in each cell and not the sheet references and formulas.

I am not sure but I imagine if I did the copy and opt leave the references then the numbers for that year will continue to change as I enter the new data for the new contract year.

Anyone out there find a work around for this

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

Andrée Starå

Hi @Dell55

I hope you're well and safe!

Strange! It should work!

Ensure that you don't have Column Formulas active in the Archive sheet.

Did that 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!

Rich Coles

Hi @Dell55 - instead of copying the sheet, create an an archive sheet and run an automation from your master sheet that automatically moves the rows to the archive when triggered by contract expiry. When copying or moving rows, it doesn't take formulas, just values. For the archive, you can simply create a new sheet, name the primary column the same as your existing sheet, and delete the extra columns. When the automation runs, it will create and populate the missing columns automatically. By starting with a new sheet, you are assured of not having anything column types (eg system generated or column formulas) that will interfere with the archive.

Rich @ Prodactive

Dell55

thank you @Andrée Starå and @Rich Coles !

Oh yeah, i was thinking i needed the column formatting for my archive sheet but I just used a blank sheet to copy the rows into ... the formulas are not there. it puts all the numbers and columns there for me and leaves out the rest

thanks you

Andrée Starå

@Dell55

Excellent!

Happy to help!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Rich Coles

Ditto - glad we could help! It's great when you realise there's such a simple solution!

Rich @ Prodactive

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.