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
Comments
-
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
-
Had the same issue today and struggled for far too long, before seeking an answer and finding this. Thank you Isaac!
-
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
-
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.
-
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.
-
Hi @Berto_DA
Can you post a screen capture of what you're seeing, and post the formula you're trying to use?Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives