Bug: EXTREMELY bizarre behavior
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
Comments
-
Paul Newcome ✭✭✭✭✭✭
Any update on this? I haven't run across this issue yet, but I'm sure it's only a matter of time.
-
J. Craig Williams ✭✭✭✭✭✭
I've run 4 tests this morning (8:10, 8:25, 8:40, 10:15) and so far, so good.
I have not experienced any of the additional odd behavior (SUM() not working!!?) but the day has just begun.
Craig
-
J. Craig Williams ✭✭✭✭✭✭
No response to my email to support sentSun, Jul 29, 2018 at 9:44 PM
Craig
Help Article Resources
Categories
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":"