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
Answers
-
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!
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.
-
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
Rich Coles
Prodactive | Smartsheet-aligned Platinum partners
Check out our Smartsheet-dedicated YouTube channel for tips, tricks and inspiration
-
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
-
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!
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.
-
Ditto - glad we could help! It's great when you realise there's such a simple solution!
Rich @ Prodactive
Rich Coles
Prodactive | Smartsheet-aligned Platinum partners
Check out our Smartsheet-dedicated YouTube channel for tips, tricks and inspiration
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!