Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.

Formules

Gilberto Santos
Gilberto Santos ✭
edited 12/09/19 inArchived 2017 Posts

Hi team!

Is it posible to increase more characters to input formules.

Look at my project:https://app.smartsheet.com/b/home?lx=UfDi3JXtI8hV-rK6MjeRWQ

· Share on Facebook Share on Twitter

Comments

  • Benjamin Walker
    Benjamin Walker Employee

    Gilberto,

    You have used a Secure Sheet Link which requires users to request access from you to view their sheet. If the information is not sensitive you may want to replace it with a publish link instead. I was unable to view your sheet to comment.

    · Share on Facebook Share on Twitter
  • Rob Hagan
    Rob Hagan ✭✭✭

    I am interested in this topic, as our largest formula is just under the 4,000 character limit. I have had to alter logic and shorten the names of fields (with the consequential flow on effect of having to update various reports that use those fields) to stay within the limit.

    On the other side of this discussion, it appears that the "savable size" of a sheet (the size above which where it won't save) is a combination of (at least) the number of rows, the number of columns and the size of formulas. Allowing larger and larger formulas may exacerbate the challenges around such "almost not savable" sheets.

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

Try this:<\/p>

=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N\/A]@row = 1), 1, 0), AVG(CHILDREN()))<\/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":108186,"type":"question","name":"Index\/Collect does not work with < symbol","excerpt":"Hi there, I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula: =INDEX(COLLECT({TotalRiskFactor}, {RangeStart}, >=[Risk Score]@row, {RangeEnd}, <=[Risk Score]@row), 1) The problem is with the \"<\" symbol in the…","snippet":"Hi there, I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula:…","categoryID":322,"dateInserted":"2023-07-27T14:55:04+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T18:38:27+00:00","insertUserID":122906,"insertUser":{"userID":122906,"name":"Cesar Perez","url":"https:\/\/community.smartsheet.com\/profile\/Cesar%20Perez","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2uFV_3dM1no!ndnWXxsFlTw!JCwGNa_KQWy","dateLastActive":"2023-07-27T15:35:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T19:06:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":27,"score":null,"hot":3380955211,"url":"https:\/\/community.smartsheet.com\/discussion\/108186\/index-collect-does-not-work-with-symbol","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108186\/index-collect-does-not-work-with-symbol","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108186,"commentID":387609,"name":"Re: Index\/Collect does not work with < symbol","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387609#Comment_387609","dateInserted":"2023-07-27T18:38:27+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T19:06:06+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JURDPT6GMX1Y\/formula.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Formula.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T15:36:00+00:00","dateAnswered":"2023-07-27T15:02:42+00:00","acceptedAnswers":[{"commentID":387556,"body":"

The issue is with your arguments overall. Using the Score of 40 (as in your examples)...<\/p>


<\/p>

The score start range has to be 40 or greater, but the range end has to be less than or equal to 40.<\/p>


<\/p>

Try reversing it so that the start is less than the score and the end is greater than the score.<\/p>


<\/p>

Take a look at the second piece in your destination file. The formula is not throwing an error, but it is also not pulling the correct value in. It should be pulling in Moderate because 40 is between 35 and 54, but it is pulling in Moderate-High because that is the first entry where the Range Start is greater than 40.<\/p>"},{"commentID":387563,"body":"

You have your greater than and less than backwards. Your formula is saying to looking for the start date to be greater than 40 and the 35 is less than 40.<\/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":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108180,"type":"question","name":"How can I make an approver's view on the message notification field mandatory?","excerpt":"Making it mandatory will not allow approver to approve or decline without acknowledgement. Example creating a column that would say: \"You acknowledged that accounting details have been reviewed?\" Then enters initials. This has to be done prior to clicking approved or decline.","snippet":"Making it mandatory will not allow approver to approve or decline without acknowledgement. Example creating a column that would say: \"You acknowledged that accounting details have…","categoryID":321,"dateInserted":"2023-07-27T14:08:57+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T18:44:32+00:00","insertUserID":157632,"insertUser":{"userID":157632,"name":"Shirley Romero","url":"https:\/\/community.smartsheet.com\/profile\/Shirley%20Romero","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/6RM6Q0PW4X07\/nLP5DK4J5PI9I.jpg","dateLastActive":"2023-07-27T15:36:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T19:06:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":19,"score":null,"hot":3380952209,"url":"https:\/\/community.smartsheet.com\/discussion\/108180\/how-can-i-make-an-approvers-view-on-the-message-notification-field-mandatory","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108180\/how-can-i-make-an-approvers-view-on-the-message-notification-field-mandatory","format":"Rich","tagIDs":[300],"lastPost":{"discussionID":108180,"commentID":387613,"name":"Re: How can I make an approver's view on the message notification field mandatory?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387613#Comment_387613","dateInserted":"2023-07-27T18:44:32+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-27T19:06:06+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T15:38:54+00:00","dateAnswered":"2023-07-27T14:43:11+00:00","acceptedAnswers":[{"commentID":387545,"body":"

This cannot be set up through an Approval Automation. At his point you have a few options (each of which has its pros and cons).<\/p>


<\/p>

You can just include a blurb that says that they are acknowledging by submitting within the Approval automation.<\/p>


<\/p>

You can set up an update request automation as a reminder that they need to enter their initials if they submit but didn't the first time.<\/p>


<\/p>

The last option I can think of that would truly make it required is a bit complex but possible. The first step is to create an \"approval sheet\". This sheet will have the same fields in it that you need to send in the approval request and a form created to match.<\/p>


<\/p>

You would then take the base form URL and drop it into a formula on the original sheet that allows you to basically generate a custom form on each row that will have the row data prefilled when someone clicks on the URL in that row. Since you can make form fields required, this will give you the ability to require their acknowledgement prior to them submitting their approval.<\/p>


<\/p>

Send this custom form URL through an alert and instruct the users to click the link to open the approval form.<\/p>


<\/p>

The form entry would populate the \"approval sheet\", but you can use formulas with cross sheet references to pull the appropriate data back over to your original sheet.<\/p>


<\/p>

If that last option sounds like something you would be interested in, fell free to let me know, and I will try to pull together a few links to help you get started building the formula.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":300,"urlcode":"Email Notifications","name":"Email Notifications"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&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 Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • Either checkbox column to contribute to % complete

    I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to…

  • Index/Collect does not work with < symbol

    Hi there, I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula: =INDEX(COLLECT({TotalRiskFactor}, {RangeStart}, >=[Risk Score]@row, {RangeEnd}, <=[Risk Score]@row), 1) The problem is with the "<" symbol in the…

  • How can I make an approver's view on the message notification field mandatory?

    Making it mandatory will not allow approver to approve or decline without acknowledgement. Example creating a column that would say: "You acknowledged that accounting details have been reviewed?" Then enters initials. This has to be done prior to clicking approved or decline.