How do I get formulas from a sheet pointing to a template to work when the template is saved as new?

Dan Heizyk
Dan Heizyk ✭
inFormulas and Functions

I've got a template built for a repeating project format and I want to be able to roll up key fields from all of the projects into some combined sheets. The formulas work when populating the original project sheet but when I save as a new sheet, the link breaks.

This is only a problem in breaking the links going from the new project to the combined sheet and not going to the new project sheet.

Tags:

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@Dan Heizyk

    目前不是一个为一张汇总recognize when new sheets are created and need to be added in to the formulas/cell links that you already have established. After creating the new sheet from the template, you'll need to go into your rollup sheets and update them to look at this newly created sheet.

    Cheers,

    Genevieve

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

@Samuel Dowdy Jr.<\/a> <\/p>

=IF([\"Will Call\" Trips]@row=0, \"Hold\", IF([\"Will Call\" Trips]@row < [\"Will Call\" Goal]@row, \"Yes\", \"No\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-23 18:18:59","updateUser":{"userID":7176,"name":"Samuel Dowdy Jr.","url":"https:\/\/community.smartsheet.com\/profile\/Samuel%20Dowdy%20Jr.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xCPC8T7_6Hw!zIfBc_qPyz4!RrIUl9SqFWX","dateLastActive":"2022-09-24T14:54:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":95851,"type":"question","name":"//www.santa-greenland.com/community/discussion/89843/\"Changes to:\" Option Not Showing Up In Automation","excerpt":"Hello, This is not populating when I am trying to add a specific person from a dropdown list: The goal is to select certain team members that will be involved on a project so we can send rows (tactics\/projects) to team member's sheets from the project lead's sheet. Can you help? Thanks, Ryan Holguin","categoryID":322,"dateInserted":"2022-09-23T15:58:25+00:00","dateUpdated":null,"dateLastComment":"2022-09-23T19:05:45+00:00","insertUserID":149803,"insertUser":{"userID":149803,"name":"Ryan Holguin","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Holguin","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ejDcJ2x9GOY!pRNd6WBKr7Q!uGy-EViqsyg","dateLastActive":"2022-09-23T19:52:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":149803,"lastUser":{"userID":149803,"name":"Ryan Holguin","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Holguin","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ejDcJ2x9GOY!pRNd6WBKr7Q!uGy-EViqsyg","dateLastActive":"2022-09-23T19:52:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3327909850,"url":"https:\/\/community.smartsheet.com\/discussion\/95851\/changes-to-option-not-showing-up-in-automation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95851\/changes-to-option-not-showing-up-in-automation","format":"Rich","tagIDs":[334],"lastPost":{"discussionID":95851,"commentID":344621,"name":"Re: \"Changes to:\" Option Not Showing Up In Automation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/344621#Comment_344621","dateInserted":"2022-09-23T19:05:45+00:00","insertUserID":149803,"insertUser":{"userID":149803,"name":"Ryan Holguin","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Holguin","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ejDcJ2x9GOY!pRNd6WBKr7Q!uGy-EViqsyg","dateLastActive":"2022-09-23T19:52:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I70LV65TG1KB\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-09-23T19:05:25+00:00","dateAnswered":"2022-09-23T17:47:08+00:00","acceptedAnswers":[{"commentID":344602,"body":"

@Ryan Holguin<\/a> <\/p>

Click the \"Add a condition to filter rows\" and then you can choose who it changes to.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

You can also define different team members to different sheets within the same automation by doing this:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

To make it look like this:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-23 19:05:25","updateUser":{"userID":149803,"name":"Ryan Holguin","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Holguin","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ejDcJ2x9GOY!pRNd6WBKr7Q!uGy-EViqsyg","dateLastActive":"2022-09-23T19:52:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":95856,"type":"question","name":"COUNTIFS with OR","excerpt":"Hello, I have spent hours trying to figure out what I am doing wrong, but I cannot seem to figure out what would get this formula to work. I am trying to count if my Simulation column says Phillips, the complete checkbox is checked and Type is Pilot OR Simulation. The formula works fine up until I add in the Type being…","categoryID":322,"dateInserted":"2022-09-23T16:42:25+00:00","dateUpdated":null,"dateLastComment":"2022-09-23T17:09:29+00:00","insertUserID":152718,"insertUser":{"userID":152718,"name":"erin.smith","url":"https:\/\/community.smartsheet.com\/profile\/erin.smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-23T20:21:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":132999,"lastUser":{"userID":132999,"name":"Frank.Smith","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Frank.Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!u2crl8Itmjo!jZnr444A07I!hA7gZMqaJJS","dateLastActive":"2022-09-23T17:48:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":44,"score":null,"hot":3327905514,"url":"https:\/\/community.smartsheet.com\/discussion\/95856\/countifs-with-or","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95856\/countifs-with-or","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":95856,"commentID":344589,"name":"Re: COUNTIFS with OR","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/344589#Comment_344589","dateInserted":"2022-09-23T17:09:29+00:00","insertUserID":132999,"insertUser":{"userID":132999,"name":"Frank.Smith","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Frank.Smith","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!u2crl8Itmjo!jZnr444A07I!hA7gZMqaJJS","dateLastActive":"2022-09-23T17:48:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-09-23T17:45:37+00:00","dateAnswered":"2022-09-23T17:09:29+00:00","acceptedAnswers":[{"commentID":344589,"body":"

Greetings Erin,<\/p>

Here is the formula I think should resolve your problem:<\/p>

=COUNTIFS(Simulation:Simulation, \"Phillips\", Complete:Complete, \"1\", Type:Type, OR(@cell = \"Pilot\", @cell = \"Simulation\"))<\/p>

For the OR statement, you can reference the @cell for your comparisons.<\/p>

Please let me know how this works.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-23 17:45:37","updateUser":{"userID":152718,"name":"erin.smith","url":"https:\/\/community.smartsheet.com\/profile\/erin.smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-23T20:21:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">