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

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

Find more posts tagged with

Cell linking

Comments

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:

Copy your formula across the desired cells
In the new cells, remove the entire cross sheet reference
Select the Reference Another Sheet hyperlink to create a new reference
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.

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.