Formula Working in Most Rows, but Incomplete in Others

Erik D. Schiller

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? 

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 support@smartsheet.com and be ready to share the sheet with them (I send them a editable published link which is often all they need)

Craig

 

CanadaJim

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 " + "-" + ".

Second, you have no final "FALSE" condition in 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