Change a cell value If a certain Postal/Zip Code is entered, is it possible?

Options

Hi there helpful people!

I am using smartsheet to track jobs in order to then export a monthly report.


I have approx 2000 postal/zip codes attributed to client sites within a particular State - and what I'm hoping to do is set up an automation (either within a workflow or a formula) where if a particular postal/zip code is entered, it will then change a cell in the next column with the name of the region that postal/zip code falls within.

Is it currently possible to do this with either a workflow or a formula?


Thanks in advance,

Josh

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Aus_CiServices

    I hope you're well and safe!

    You can add all the postal/zip codes to a separate sheet in one column and the matching Region in the next. You could then use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when a postal/zip code is selected, it will populate the Region.

    I'd recommend INDEX/MATCH.

    This is the structure.

    =
    INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row;
    {ColumnWithTheValueToMatchAgainsTheCell}; 0))
    

    Depending on your country/Region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!