Second reference is updating the first reference in another formula
I have a formula that looks up a Sheet to calculate Days Since Submitted. I have ANOTHER sheet where we move all Closed jobs once complete. I want to look that sheet up to calculate Days from Start to Completion. When I write that formula, the reference in my first formula changes. Why? How can I stop it? Thanks in advance
Best Answer
-
Hey,
I can understand the frustration, there is no problem with copying your formula but when you change the source highlight the source name and then instead of saying Edit Reference it will say Reference Another Sheet.
this way it will only change the specific formula and not the reference across the sheet.
Glad it worked for you!
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
Answers
-
Hey,
When you write the second formula, do you copy the first and click Edit Reference?
Can you share some screenshots by any chance?
Itai
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Hey, so here is formula 1
=AVG(COLLECT({Days Since Created}, {Priority}, [Column2]$21))
When I change {Days Since Created} to another range from another sheet, it edits the row I've already calculated, and a bunch of other Metrics I set up. I don't know why it changes all formula with that reference, and not just that cell....???
Ok, so I was copying, and then just changing the reference; guess Smartsheet doesn't like that. I have just tried it and retyped and it worked :-)
-
Hey,
I can understand the frustration, there is no problem with copying your formula but when you change the source highlight the source name and then instead of saying Edit Reference it will say Reference Another Sheet.
this way it will only change the specific formula and not the reference across the sheet.
Glad it worked for you!
Itai Perez
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Ah! Ok! The subtleties of Smartsheet 😆
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!