Individual formula editing changes the whole column
Not sure if this is supposed to happen, but since i was not expecting it at all, i found it extremely frustrating.
So i have a spreadsheet that is using several index/match formulas on different columns to pull information from other spreadsheets.
But today, one of the Admins on another spreadsheet made me aware that he was going to add some old records that were needed.
These old records must be found using different references, so i have to edit the index/match formula on those specific rows.
What happened was that, by editing just one formula on one cell, and going back a forth between spreadsheets while doing it, i noticed that the formula applied to that column was completely edited, and its now obsolete. (Note: when i first typed it, was just in one cell, and then expanded it to all the others)
It's not a big hassle, but is this supposed to happen?
Comments
-
Hi Jose,
It sounds to me you accidentally updated the reference.
More info:
Edit a Reference
You can edit the name, referenced cell range, and source sheet of the reference.
NOTE: You must have Editor-level sharing or greater permissions on a sheet to edit the reference. You must also have Viewer-level sharing permissions or greater on the sheet that contains the data you want to reference.
- Select the cell that contains your formula. A summary of the formula appears on the left or right of the cell.
- Click the name of the reference that you’d like to edit. The Reference Another Sheet form appears.
Once in the Reference Another Sheet form, you can change the reference name, or choose a new sheet or range of cells. When you edit a reference, all instances of that reference on the sheet are updated automatically to reflect the change.
Hope that helps!
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
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.
- Select the cell that contains your formula. A summary of the formula appears on the left or right of the cell.
-
Now i fixed it but...is there any way that i edit the reference on just a handfull of cells in a column, leaving all the others as they are?
-
Yes, but you would have to create a new reference not to change the other one.
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.
-
Yeah, i get it now.
In the meantime i discovered the real source of the problem.
I learned that if i am using a reference named "Sample registration 1", and if i repeat that same reference name on another formula, it will actually edit them both.
tbh it could trigger a warning message, advising that i am changing a previously set reference, but i will submit an enhacement request for that.
Thanks!
-
Excellent!
Happy to help!
A warning message is an excellent idea!
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!