Formula within cell

ICJ2025

Hi there,

Thank you for helping me out!

I'm working with a SS that contains some specific information.

Due to internal regulations, each new job requested must contain a URN, which I would like to be automatically generated based on information from the existing sheet.

The format I would like is as follows:

[Stand type] first 3 letters from the left_Requested date DDMMYYYY_[Sequence]000

This is the formula I am trying to input currently:

=UPPER(LEFT([Stand type]@row , 3)) & "_" & TEXT([Date Requested]@row , "ddmmyyyy") & "_" & [Sequence]@row

The column names are correct (Stand type, Date Requested) but for some reason it's not liking it.

I'm trying to add this into a cell.

Any suggestions?

Find more posts tagged with

Accepted answers

ICJ2025

HI Paul,

I just managed to figure it out.

It seems that SS does not like the auto number fucntion within a cell, so I had to do a a HELPER column with auto numbering, and then the formula calls the the numbering from the HELPER column.

The formula looks like this:

=UPPER(LEFT([Stand type]@row , 3)) + "_" + RIGHT("0" + DAY([Date Requested]@row ), 2) + RIGHT("0" + MONTH([Date Requested]@row ), 2) + YEAR([Date Requested]@row ) + "_" + [Auto ID]@row

All comments

Paul Newcome

Formula syntax in Smartsheet has some differences from the syntax in Excel. Give this a try:

=UPPER(LEFT([Stand type]@row, 3)) + "_" + RIGHT("0" + DAY([Date Requested]@row), 2) + RIGHT("0" + MONTH([Date Requested]@row), 2) + YEAR([Date Requested]@row) + "_" + [Sequence]@row

ICJ2025

Thank you Paul.

Still no luck with the above suggestion.

Paul Newcome

Are you getting an error or an unexpected output? Are you able to provide a screenshot of the formula open in the cell as if you are about to edit it?

ICJ2025

=UPPER(LEFT([Stand type]@row , 3)) + "_" + RIGHT("0" + DAY([Date Requested]@row ), 2) + RIGHT("0" + MONTH([Date Requested]@row ), 2) + YEAR([Date Requested]@row ) + "_" + [Auto ID]@row

Paul Newcome

Happy to help. 👍️

Marcela

Hi @ICJ2025 Glad you figured it out!

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.