I want to stop updating all instances of cross-sheet reference

Hello,

Thanks for your post! It sounds like you're trying to create multiple cross sheet formulas with references to different columns in another sheet, but when you update the reference, all of the formulas look at the same column from the other sheet. 

If this is the case, it sounds like you're copying the formula, selecting the cross sheet reference in the new cell, then opening the Edit Reference form with the hyperlink to change the column. When you edit an existing reference, all instances of that references in the sheet are updated.

In order to reference different column in different cells, you'll want to create a new Cross Sheet Reference:

1. Copy your formula across the desired cells
2. In the new cells, remove the entire cross sheet reference
3. Select the Reference Another Sheet hyperlink to create a new reference
4. Highlight the desired column and create the reference

This will allow you to reference different columns in each formula. You can have up to 25 cross sheet references in a sheet, and you can reference up to 25,000 unique cells.

Bonus Tip: When working with multiple cross sheet references in a single sheet, you'll want to name them intuitively so that you know what the reference is without opening the Edit Reference form. By the default, references are named "{Sheet Name Range X}" but you can change the name in the top left corner of the Create or Edit Reference form. I like to use the sheet name + column name for my cross sheet references.

More information about Cross Sheet References can be found here: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Kind regards,

Isaac J

Smartsheet Support

Hi Neisha,

So glad to hear that I helped out! My goal for these is always to provide a response that will help others that seek guidance (:

Have a great day!

Isaac J

Unfortunately for me, not working at all. I'm even writing the formula from scratch and I keep getting errors, like #INVALID COLUMN NAME on top of other cell references automatically changing.

Thank you, Genevieve. I resolved by manually typing the formula in each cell. Laborious, but it worked.

But if there are any posts you know of regarding how to avoid unwanted global updates to a sheet's formula, please share.