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

henryling

Hello,



I am working on a table and am using a countif formula that references another sheet. I then want to copy paste this same formula across a few more cells but would like to change the reference column for each new cell. However, when I change the reference column on one cell, it then updates all instances using the same formula. So now all the cells are referencing the new cell. Is there a work around this? It's time consuming having to rewrite and reference new columns for each cell.



Thank you

Isaac Jose

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

Neisha Fredericks

Had the same issue today and struggled for far too long, before seeking an answer and finding this. Thank you Isaac!

Isaac Jose

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

TJS

https://community.smartsheet.com/discussion/comment/109701#Comment_109701

I know this is an old thread, but thanks for the tip on naming the sheet reference. This made it really easy to update the references in my massive nested IF statements.

Berto_DA

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.

Genevieve P.

Hi @Berto_DA
Can you post a screen capture of what you're seeing, and post the formula you're trying to use?

Berto_DA

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.