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?

Tags:

Best Answer

Answers

  • Bassam Khalil
    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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭
    edited 08/12/21

    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
    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
    Adrian Mandile CHESS ✭✭✭✭✭
    Answer ✓

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!