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.
Formula Working in Most Rows, but Incomplete in Others
We have a sheet that automatically generates a job number based on how someone fills out a web form:
=IF([CLIENT - REGION]1 = "HMA - Hyundai Motor America", "HMA", IF([CLIENT - REGION]1 = "HDAA - Hyundai Dealer Association", "HDA", IF([CLIENT - REGION]1 = "ADI - Adidas", "ADI", IF([CLIENT - REGION]1 = "MOGUL - Federal Mogul Motorparts Corp (Champion)", "CHA", IF([CLIENT - REGION]1 = "FINLNE - Finish Line", "FL", IF([CLIENT - REGION]1 = "IWA - Innocean Worldwide", "IWA", IF([CLIENT - REGION]1 = "HDAA - Hyundai Sponsored HDAA Events", "HDA", IF([CLIENT - REGION]1 = "HDE - Hyundai Dealer East", "HDE", IF([CLIENT - REGION]1 = "HDC - Hyundai Dealer Central", "HDC", IF([CLIENT - REGION]1 = "HDW - Hyundai Dealer West", "HDW", IF([CLIENT - REGION]1 = "HDSC - Hyundai Dealer South Central", "HDSC", IF([CLIENT - REGION]1 = "HDS - Hyundai Dealer South", "HDS", IF([CLIENT - REGION]1 = "HKOOK - Hankook", "HAN", IF([CLIENT - REGION]1 = "KIA - Kia", "KIA")))))))))))))) + "-" + IF([MEDIA TYPE]1 = "A - Admin", "A", IF([MEDIA TYPE]1 = "AS - Auto SHow", "AS", IF([MEDIA TYPE]1 = "B - Broadcast", "B", IF([MEDIA TYPE]1 = "C - Brochures", "C", IF([MEDIA TYPE]1 = "CRM - CRM", "CRM", IF([MEDIA TYPE]1 = "D - Deler Materials (POS)", "D", IF([MEDIA TYPE]1 = "F - Running Footage", "F", IF([MEDIA TYPE]1 = "I - Interactive", "I", IF([MEDIA TYPE]1 = "M - Misc.", "M", IF([MEDIA TYPE]1 = "O - Out of Home", "O", IF([MEDIA TYPE]1 = "P - Print", "P", IF([MEDIA TYPE]1 = "R - Radio", "R", IF([MEDIA TYPE]1 = "S - Dubs and Shipping", "S", IF([MEDIA TYPE]1 = "T - Talent", "T")))))))))))))) + IF([Billing Year]1 = "2017", "17", IF([Billing Year]1 = "2018", "18")) + "-" + IF(PRODUCT1 = "Accent (HC)", "HC", IF(PRODUCT1 = "Brand (BR)", "BR", IF(PRODUCT1 = "Elantra(AD)", "AD", IF(PRODUCT1 = "Elantra GT (PD)", "PD", IF(PRODUCT1 = "Freelance (frelan)", "frelan", IF(PRODUCT1 = "G90 (HI)", "HI", IF(PRODUCT1 = "G80 (DH)", "DH", IF(PRODUCT1 = "G70 (IK)", "IK", IF(PRODUCT1 = "Kona (OS)", "OS", IF(PRODUCT1 = "Ioniq (AE)", "AE", IF(PRODUCT1 = "IWA (IWA)", "IWA", IF(PRODUCT1 = "Misc (M)", "M", IF(PRODUCT1 = "Multi Product (MP)", "MP", IF(PRODUCT1 = "OLC (OLC)", "OLC", IF(PRODUCT1 = "Prospect List (PM)", "PM", IF(PRODUCT1 = "Santa Fe (NC)", "NC", IF(PRODUCT1 = "Sante Fe Sport (AN)", "AN", IF(PRODUCT1 = "Sonata (LF)", "LF", IF(PRODUCT1 = "Time Tracking (T)", "T", IF(PRODUCT1 = "Tucson (TL)", "TL", IF(PRODUCT1 = "Veloster (FS)", "FS", IF(PRODUCT1 = "18 Veloster (JS)", "JS", IF(PRODUCT1 = "Santa Fe (TM)", "TM"))))))))))))))))))))) + "-" + [Webvantage Job Number]1))
In most of the rows, it has no problem generating the number. However, it is inconsistently having issues regenerating the number once the [Webvantage Job Number] field is updated.
Any insight on why this his happening?
Comments
-
J. Craig Williams ✭✭✭✭✭✭First, I'm pretty sure I wouldn't do it that way. If you add a Client Region, Media Type, or Product, you will need to update your formula, at the very least in the last two rows so that new rows will auto-fill with the correct formula.
Second, check:
1. formula in the offending cell - does it have the correct cell references?
2. does the cell have data?
The results in rows 3, 4, and 5 are different. Row 3 may or may not be pointing to a blank [Webvantage Job Number]3 cell reference, but why does row 4 not have the last "-"?
If all else fails, contact[email protected]and be ready to share the sheet with them (I send them a editable published link which is often all they need)
Craig
-
Hi Erik.
I noticed a couple of things in your formula.
First, there are two ")" missing from the last set of IF statements for PRODUCT1. So take the two after "[Webvantage Job Number]1" and put them in front of the previous " + "-" + ".
第二,你没有我最终“FALSE”条件n any of your nested IFs. The last IF should have an extra comma, followed by a value to be used if the condition is not met. For example, if PRODUCT1 is something other than the options presented, you may want to add ", "Unknown Product"" inside the last IF statement.
As Craig mentioned, this method is very manual and not recommended. It would be better to create a table on the same sheet or another sheet for each of these categories, then use a single LOOKUP statement instead of the nested IF statements. Then if you need to update the list, it can be done in one easy to read place instead of inside a formula.
Good luck!
Jim
-
J. Craig Williams ✭✭✭✭✭✭
Good catch on the first CJ. I didn't even bother to look at the formula itself. My eyes glaze over if the formula gets longer than 100 characters or so.
I partially agree with your second comment on FALSE statements, but would not stress the SHOULD part. Many of my IF() statements are OK without them because a blank is as good as an warning value like Unknown Product. When building for customers, I usually put them in, based on their skill spotting those blanks as possible mistakes or omissions.
Craig
Categories
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
