Bug: EXTREMELY bizarre behavior

J. Craig Williams
J. Craig Williams ✭✭✭✭✭✭
edited 12/09/19 inFormulas and Functions

After the release a little while ago, the first thing I noticed was a simple sum of 7 cells was giving the incorrect value. That resolved itself when I closed and reopened the Sheet for the second time.

And then the KPI's I was working on went 'bonkers' and I noticed the attached values coming from a calculation on a cell that had a cross-sheet reference.

Further testing revealed that a formula like this:

=IFERROR(some formula based on text coming from x-sheet ref, "")

was blank. The cell used in the formula was OK.

Changing the formula to this

=IFERROR(some formula based on text coming from x-sheet ref, "err")

resulted in the formula returning the correct value (not "err")

I have a video to send to support.

My formulas are returning incorrect values. X-Sheet References are toggling (and apparently have been doing so all weekend as I worked)

I'll try again in the morning.

Craig

X-Sheet_References_Toggling.png

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @Lauryn Vogt<\/a>,<\/p>

You don't need the square brackets [ ] around false in your lookup - these will definitely cause an error.<\/p>

If the parent item is listed first, it should capture this item in the lookup. If that isn't the case, you can get round this by using an INDEX\/COLLECT instead. Unfortunately I can't really say what this would look like without column headers on the respective sheets.<\/p>

Hope this helps, but if you've any problems\/questions then just post! 🙂<\/span><\/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":111578,"type":"question","name":"Help with Conditional Index Match","excerpt":"Hello, I have a sheet titled Copy\/Paste Data with a column that has multiple text values. I have another sheet titled 23-24 Status. I am trying to conduct an index match on sheet titled Copy\/Paste Data when a specific column within this sheet contain the text SNP but keep getting #UNPARSEABLE error. =IF({Copy\/Paste Data…","snippet":"Hello, I have a sheet titled Copy\/Paste Data with a column that has multiple text values. I have another sheet titled 23-24 Status. I am trying to conduct an index match on sheet…","categoryID":322,"dateInserted":"2023-10-12T14:40:30+00:00","dateUpdated":null,"dateLastComment":"2023-10-12T18:15:34+00:00","insertUserID":145651,"insertUser":{"userID":145651,"name":"MichaelO1","url":"https:\/\/community.smartsheet.com\/profile\/MichaelO1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-12T19:49:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":145651,"lastUser":{"userID":145651,"name":"MichaelO1","url":"https:\/\/community.smartsheet.com\/profile\/MichaelO1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-12T19:49:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":34,"score":null,"hot":3394258564,"url":"https:\/\/community.smartsheet.com\/discussion\/111578\/help-with-conditional-index-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111578\/help-with-conditional-index-match","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":111578,"commentID":399753,"name":"Re: Help with Conditional Index Match","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399753#Comment_399753","dateInserted":"2023-10-12T18:15:34+00:00","insertUserID":145651,"insertUser":{"userID":145651,"name":"MichaelO1","url":"https:\/\/community.smartsheet.com\/profile\/MichaelO1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-12T19:49:16+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-10-12T18:55:08+00:00","dateAnswered":"2023-10-12T17:50:18+00:00","acceptedAnswers":[{"commentID":399736,"body":"

It looks like you are going to need an INDEX\/COLLECT instead. Try this:<\/p>

=INDEX(COLLECT({Copy\/Paste Data Range 2}, {Copy\/Paste Data Range 3}, @cell = CEID@row, {Copy\/Paste Data Range 1}, @cell = \"SNP\"), 1)<\/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":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]},{"discussionID":111571,"type":"question","name":"Not counting based on multiple factors","excerpt":"I'm building a formula that uses the contents in a cell to effect a percentage. Basically: =(count all cells within a range with the word \"PASS\") (divide this number by) (All the cells within same range that are NOT blank ) The piece of the formula that looks at the row and avoids blank cells looks like this:…","snippet":"I'm building a formula that uses the contents in a cell to effect a percentage. Basically: =(count all cells within a range with the word \"PASS\") (divide this number by) (All the…","categoryID":322,"dateInserted":"2023-10-12T13:35:09+00:00","dateUpdated":null,"dateLastComment":"2023-10-12T17:25:08+00:00","insertUserID":147576,"insertUser":{"userID":147576,"name":"jblunda","url":"https:\/\/community.smartsheet.com\/profile\/jblunda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pafraWu5tDM!w6fq9-v6BDM!XIcicgnpuVa","dateLastActive":"2023-10-12T17:21:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":147576,"lastUser":{"userID":147576,"name":"jblunda","url":"https:\/\/community.smartsheet.com\/profile\/jblunda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pafraWu5tDM!w6fq9-v6BDM!XIcicgnpuVa","dateLastActive":"2023-10-12T17:21:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":32,"score":null,"hot":3394251617,"url":"https:\/\/community.smartsheet.com\/discussion\/111571\/not-counting-based-on-multiple-factors","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111571\/not-counting-based-on-multiple-factors","format":"Rich","tagIDs":[254,537],"lastPost":{"discussionID":111571,"commentID":399732,"name":"Re: Not counting based on multiple factors","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399732#Comment_399732","dateInserted":"2023-10-12T17:25:08+00:00","insertUserID":147576,"insertUser":{"userID":147576,"name":"jblunda","url":"https:\/\/community.smartsheet.com\/profile\/jblunda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pafraWu5tDM!w6fq9-v6BDM!XIcicgnpuVa","dateLastActive":"2023-10-12T17:21:11+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-10-12T17:23:29+00:00","dateAnswered":"2023-10-12T14:46:35+00:00","acceptedAnswers":[{"commentID":399687,"body":"

Hi @jblunda<\/a> <\/p>

COUNTM will count the number of elements in non blank cells or range<\/p>

=COUNTM(Column1@row:Column2@row)<\/p>

=COUNTM(Column1:Column1)<\/p>

Hope this helps!<\/p>"},{"commentID":399730,"body":"

@jblunda<\/a> <\/p>

See if this works with the additional parameter (change \"Column1\" to your column name)<\/p>

=COUNTIF([Column1]:[Column1], \"PASS\") \/ COUNTIFS([Column1]:[Column1], <>\"//www.santa-greenland.com/community/discussion/29516/\", [Column1]:[Column1], <>\"N\/A\")<\/p>

If the range is 2 columns next to each other:<\/p>

=COUNTIF([Column1]:[Column2], \"PASS\") \/ COUNTIFS([Column1]:[Column2], <>\"//www.santa-greenland.com/community/discussion/29516/\", [Column1]:[Column2], <>\"NA\")<\/p>

This is looking at the entire column, if you need to look at a specific range of rows, add the row numbers like this: [Column1]1<\/strong>:[Column1]7<\/strong><\/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"},{"tagID":537,"urlcode":"logic","name":"logic"}]}],"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