Why does 'range' number update?

max1074

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?

Andrée Starå

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!

max1074

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å

@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!

max1074

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å

@max1074

You're more than welcome!

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

Jason P

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.

Genevieve P.

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