Why does 'range' number update?

Options

My formula previously was; =COUNTIF({Range 1}, CONTAINS("Name", @cell)) which worked perfectly. All of a sudden this stopped working.

When I update to; =COUNTIF({Range 4}, CONTAINS("Name", @cell)) it works again.

Why would the range number have changed and how do I fix it?

Answers

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

    Hi @max1074

    I hope you're well and safe!

    Strange!

    Have you tried changing the range name back to 1?

    Pro-tip, I'd recommend naming it something more specific, so you or someone else understands what it is.

    Did that work?

    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, Awesome, 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.

  • max1074
    max1074 ✭✭
    Options

    Hi @Andrée Starå,

    When it says 'range 1' the formula output is '#invalid ref'. As this appeared everywhere I had to locate the issue and it was the 'name' field.

    I rewrote the formula from scratch and then the 'name' field appeared as 'range 4' opposed to 'range 1'. When this was updated for all formulas (range 1 changing to range 4), the formula worked again.

    I have no idea why this would have changed...

    When you note renaming it to something more specific, how is this carried out? As I am lifting data from a different Smartsheet into a calculation sheet, it automatically pulls it through as 'range' and then a number.

    Thanks for your support so far,

    Max

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

    @max1074

    Happy to help!

    When you select/add the range, you can rename it to something else.

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome 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.

  • max1074
    max1074 ✭✭
    Options

    Hi @Andrée Starå,

    Do we know why the name might have changed and now needs updated? Could it be do with any columns being added, etc (although from activity log, I don't think any changes have been made to columns).

    I just don't want this occurring again.

    Thanks,

    Max

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

    @max1074

    You're more than welcome!

    No, if you or someone else didn't change it, it should not update.

    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.

  • Jason P
    Jason P ✭✭✭✭
    Options

    Hi All.

    If I'm reading correctly I have simular issues.

    I've discovered that on the same sheet, there is another cross-sheet formula that references Range 1, eg a date range.

    While creating the new cross-sheet formula, I might have chosen the same date column. However, Smartsheet automatically assigned it to Range 2. As soon as I executed the formula, all the cells began to "calculate", which sometimes resulted in errors like #unparseable, #invalid or 0. It's caused some heart palpitations and sweats... I was not aware of the renaming could help here.

    Cheers.

    Forever forwards Backwards never.

  • Genevieve P.
    Options

    Hi @Jason P

    If you select a column that's already being used in your sheet somewhere else, this should then automatically use the same name as the previous reference. You can't have unique references to the same column... it will be the same reference (does that make sense?)

    What may happen is if you click edit for a currently used reference and change what column it's looking at, this will update that same named reference across your entire sheet.

    To fix this, simply Edit the reference and point it back to the correct location. Then create an entirely new reference looking at the different column. 🙂

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!