How to drag formula and prevent cells linking together reference sheets?

Kristi

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)?

Paul Newcome

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.

Paul Newcome

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

Kristi

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

Paul Newcome

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.

Kristi

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.

Paul Newcome

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.

Kristi

That does it! Thank you for your help!!

Paul Newcome

Great! Happy to help! 👍️

Mike Mills

Figured my question out. But don't see an option to remove my comment/question only edit.