Second reference is updating the first reference in another formula

nualamcd

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

Itai Perez

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

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

nualamcd

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 :-)

Itai Perez

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!

nualamcd

Ah! Ok! The subtleties of Smartsheet 😆