How to drag formula and prevent cells linking together reference sheets?
When I enter a formula (e.g countif) that references another sheet, and then drag this formula to the next rows and try to change the sheet referenced, it changes all the cells as if the formula is linked between all the cells. How do I replicate a formula I want to be in multiple rows but then edit each row to reference a different sheet within that same formula (without it changing/linking all of them together)?
Best Answer

Right. You do not want to chose to "Edit" the reference. You want to delete the reference and then add in the new one. You can select only the reference.
=COUNTIFS({Sheet 1 Column}, @cell = "Something")
Dragfill your formula then only highlight the cross sheet reference
{Sheet 1 Column}
The helper box below should change from "Edit Reference" to "Reference Another Sheet", but you have to select the entire reference including the curly brackets.
This will allow you to insert the new reference while leaving the rest of the formula intact.
Answers

You would delete that occurrence of the reference and then create a new one.

Thanks! So do you have to reenter the formula in each cell if each one references a different sheet?

No. I actually do this rather frequently. I go ahead and dragfill the formula, then I go into each of the cells and I only have to replace the cross sheet reference(s) that need to be updated. This saves a lot of time especially on longer and/or more complex formulas.

That's what I've been trying but for some reason when I drag the formula and then update the reference sheet in the new cell, it changes the previous cell too, even though they're supposed to have different reference sheets. The only work around I've found is to just clear the contents and retype the formula....which obviously is not ideal.

Right. You do not want to chose to "Edit" the reference. You want to delete the reference and then add in the new one. You can select only the reference.
=COUNTIFS({Sheet 1 Column}, @cell = "Something")
Dragfill your formula then only highlight the cross sheet reference
{Sheet 1 Column}
The helper box below should change from "Edit Reference" to "Reference Another Sheet", but you have to select the entire reference including the curly brackets.
This will allow you to insert the new reference while leaving the rest of the formula intact.

That does it! Thank you for your help!!

Great! Happy to help! 👍️

Figured my question out. But don't see an option to remove my comment/question only edit.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!