Lookup Value Issues

I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that formatting as I think that is throwing off my lookup. I made this helper Column DID to help turn that Dealer ID into a string.

On Sheets B and C however I need to pull the Code out of the Name of the Retailer. If the code is not 6 digits and only 5 digits, a 0 needs added in front to get to 6 digits.

Using this formula to get the code either 5 or 6 digits

And then using this formula to make sure all codes are 6 digits long

I cannot seem to get these codes to sync up. The ultimate goal is to pull in new naming conventions from Sheet A into Sheet B and C using these dealer codes.

Any suggestions or help would be greatly appreciated.

Find more posts tagged with

Sheets

Formulas

functionality

Cell linking

Accepted answers

All comments

Jgorsich

I recently ran into a similar problem helping someone else setup a very similar lookup table. It turns out that Smartsheet will - even if you add a ' before a number - treat anything that looks like a number AS a number in MOST situations. It caused a similar breakdown; MOST of the codes would pull correctly but there was 1 that wouldn't. The workaround that we ended up landing on was adding a helper column to the to the primary sheet (similar to your "DID" column) but adding an actual letter in place of the "". Then, when looking it up, we pulled "a"+actualValue to perform the lookup against - weirdly, that worked better than "" to force the program to see it as a string.

Try this:

change your "DID" column formula on sheet A to be ="a" + [DealerID]@row

and your "DID" column formulas for sheets B and C to be =if(len(left(retailer@row, find("-",retailer@row)-1))=6, "a" + left(retailer@row , find("-",retailer@row )-1), "a0" + left(retailer@row , find("-",retailer@row )-1))

and see if that works for you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.