Why does 'range' number update?
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
-
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.
-
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
-
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.
-
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
-
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.
-
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.
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!