How can i manually enter range values in a formula

I would like to manually enter a list of values in the range parameter for an INDEX formula, as follows:

= INDEX (range , row/col number )

= INDEX ( {"Jan";"Feb";"Mar"}, 2 )

= "Feb"

In this example, I don't want to have a list of month abbreviations somewhere else on the sheet, or as an external reference to another sheet. I want to be able to manually list the string or number values in a similar syntax to how Excel would (with the curly brackets and semicolons). Is there a way to do this in Smartsheet?

Find more posts tagged with

Formulas

Accepted answers

Adrian Mandile CHESS

Thanks @Paul Newcome,

That's what I thought also. I was just hoping there would be someway to replace the [Text Column]:[Text Column] part with an array of string values instead of having them float around in my Sheet (or an external lookup Sheet) somewhere.

All comments

Bassam Khalil

Hi @Adrian @Chess

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Or if you like to fix the formula directly on your sheet please share me as an admin on a copy of your sheets ( Source & Destination ) and i will write the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Paul Newcome

What would be the reason for this as opposed to just creating the cross sheet reference?

Adrian Mandile CHESS

Hi @Bassam Khalil and @Paul Newcome,

Thanks for your prompt replies.

I am trying to create a date-based and naturally-ordered label for a chart that includes a numeric year/month with the month abbreviations "Jan", "Feb", etc. followed by the day, such as for a date of 13/8/2021 it calcs/displays "21.08 AUG 13".

I want this formula/sheet to stand alone and not be linked to another reference sheet.

I have managed to create the desired result using deeply nested IF statements, as follows:

=RIGHT(Date@row, 2) + "." + MID(Date@row, 4, 2) + " " + UPPER(IF(MONTH(Date@row) = 1, "Jan", IF(MONTH(Date@row) = 2, "Feb", IF(MONTH(Date@row) = 3, "Mar", IF(MONTH(Date@row) = 4, "Apr", IF(MONTH(Date@row) = 5, "May", IF(MONTH(Date@row) = 6, "Jun", IF(MONTH(Date@row) = 7, "Jul", IF(MONTH(Date@row) = 8, "Aug", IF(MONTH(Date@row) = 9, "Sep", IF(MONTH(Date@row) = 10, "Oct", IF(MONTH(Date@row) = 11, "Nov", "Dec")))))))))))) + " " + LEFT(Date@row, 2)

However, I would prefer to use the INDEX function (shown above) if possible, but replacing a selected 'range' with the actual list values (as month short names will never change). Excel also has a CHOOSE function which would serve a similar need but is not available in Smartsheet.

PS: This is for Australian format dates stored in Smartsheet as 13/08/21 ("dd/mm/yy"), not US "mm/dd/yy".

Paul Newcome

The only two ways I can see to make this work would be that we can simplify your existing formula (slightly) or you would need to create a table somewhere that you can reference to be able to use an INDEX/MATCH.

If your table looks like this...

1.....JAN

2.....FEB

3.....MAR

so on and so forth

You would be able to replace the month text portion of your formula with

INDEX([Text Column]:[Text Column], MATCH(MONTH(Date@row), [Number Column]:[Number Column], 0))

Adrian Mandile CHESS

Thanks @Paul Newcome,

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.