Formulas Auto-Populating in a Newly Added Row

rkrahn
rkrahn ✭
edited 12/09/19 inFormulas and Functions

Everytime I add a new row in my sheet, I have to go in and make the formula go onto that new row. Is there a way to have the formula auto-populate whenever I add a new row into the sheet?

Tags:
· Share on Facebook Share on Twitter

Comments

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi @l.gann<\/a> <\/p>

I hope you're well and safe!<\/p>

You could add a so-called helper column and reference a specific cell for check all and have a workflow trigger from it, and that would check all the boxes.<\/p>

Make sense?<\/p>

Would that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":448,"urlcode":"workflows-in-smartsheet","name":"Workflows in Smartsheet"}]},{"discussionID":108069,"type":"question","name":"Sumifs formula only if all columns contain a value","excerpt":"I have sheet that is \"scoring\" projects across 6 criteria. The criteria all have numeric values associated with them from 1-4. I want to sum the values for all the criteria, but ONLY IF all columns have a value. Meaning, if someone fails to score one of the criteria, I want the sum column to be blank vs it summing the…","snippet":"I have sheet that is \"scoring\" projects across 6 criteria. The criteria all have numeric values associated with them from 1-4. I want to sum the values for all the criteria, but…","categoryID":322,"dateInserted":"2023-07-25T22:11:33+00:00","dateUpdated":null,"dateLastComment":"2023-07-25T22:28:45+00:00","insertUserID":161473,"insertUser":{"userID":161473,"name":"GHustad1","title":"Ms","url":"https:\/\/community.smartsheet.com\/profile\/GHustad1","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!enMrR4fLEjs!5Qi0-a0_byQ!_Qf5vs03__K","dateLastActive":"2023-07-25T22:26:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":116407,"lastUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DKTEWPYCU2OY\/nBJQNCZXABT0R.png","dateLastActive":"2023-07-26T14:31:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3380649018,"url":"https:\/\/community.smartsheet.com\/discussion\/108069\/sumifs-formula-only-if-all-columns-contain-a-value","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108069\/sumifs-formula-only-if-all-columns-contain-a-value","format":"Rich","lastPost":{"discussionID":108069,"commentID":387050,"name":"Re: Sumifs formula only if all columns contain a value","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387050#Comment_387050","dateInserted":"2023-07-25T22:28:45+00:00","insertUserID":116407,"insertUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DKTEWPYCU2OY\/nBJQNCZXABT0R.png","dateLastActive":"2023-07-26T14:31:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-25T22:27:20+00:00","dateAnswered":"2023-07-25T22:25:51+00:00","acceptedAnswers":[{"commentID":387047,"body":"

@GHustad1<\/a> It's easiest to check for blank cells in the range, then sum if there are none, like this:<\/p>


<\/p>

=IF(COUNTIF([Retail \/ Non-Retail Helper Column]@row:[Resources Required - Helper Column]@row, ISBLANK(@cell)) = 0, SUM([Retail \/ Non-Retail Helper Column]@row:[Resources Required - Helper Column]@row), \"//www.santa-greenland.com/community/discussion/29786/\")<\/p>


<\/p>

The formula counts the number if blank cells in the range, if there is at least one, the count will NOT equal 0, and it will insert \"//www.santa-greenland.com/community/discussion/29786/\", else if there are values in all cells, it sums the range.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":108019,"type":"question","name":"Getting MEDIAN to work after IF statement","excerpt":"All data is in the same worksheet. Days to Live is a column formula calculation that usually has a number, but sometimes an error, because there is no live date and it's a column formula (this is fine for me as I don't show it directly). =MEDIAN({Days to Live}) works fine, even when there are records included that have no…","snippet":"All data is in the same worksheet. Days to Live is a column formula calculation that usually has a number, but sometimes an error, because there is no live date and it's a column…","categoryID":322,"dateInserted":"2023-07-25T08:52:55+00:00","dateUpdated":null,"dateLastComment":"2023-07-26T08:21:11+00:00","insertUserID":136416,"insertUser":{"userID":136416,"name":"Alex Gardner","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Gardner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T13:25:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T15:21:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3380636646,"url":"https:\/\/community.smartsheet.com\/discussion\/108019\/getting-median-to-work-after-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108019\/getting-median-to-work-after-if-statement","format":"Rich","lastPost":{"discussionID":108019,"commentID":387096,"name":"Re: Getting MEDIAN to work after IF statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387096#Comment_387096","dateInserted":"2023-07-26T08:21:11+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-26T15:21:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-26T11:41:18+00:00","dateAnswered":"2023-07-25T09:20:04+00:00","acceptedAnswers":[{"commentID":386768,"body":"

Hi @Alex Gardner<\/a>,<\/p>

A formula like this should work:<\/p>

=MEDIAN(COLLECT({Days to Live},{Relationship Manager},\"John Doe\"))<\/p>

In a table with names in one column you can obviously substitute a row reference in place of the name.<\/p>

Hope this helps, if you still have any problems then just post!<\/p>"},{"commentID":387096,"body":"

No problem at all, happy to have helped!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Auto check all check boxes in a column

    Hello! I have a sheet with a check box column where end users select the check box to nominate a speaker which will trigger a separate workflow. The issue with individually selecting is that there can be upwards of 100 speakers on the list. Is there a way to have an option for the end user to click one check box or choose…

  • Sumifs formula only if all columns contain a value

    I have sheet that is "scoring" projects across 6 criteria. The criteria all have numeric values associated with them from 1-4. I want to sum the values for all the criteria, but ONLY IF all columns have a value. Meaning, if someone fails to score one of the criteria, I want the sum column to be blank vs it summing the…

  • Getting MEDIAN to work after IF statement

    All data is in the same worksheet. Days to Live is a column formula calculation that usually has a number, but sometimes an error, because there is no live date and it's a column formula (this is fine for me as I don't show it directly). =MEDIAN({Days to Live}) works fine, even when there are records included that have no…