Deleting one outgoing cross-sheet reference ended in ALL references from the same sheet being delete

JLC

Hello,



I spoke to Support on the phone yesterday but we haven't had a definitive answer on what the expected behaviour for this scenario is. I'm curious to see what other users have experienced.

In the the "Edit or Remove links" > "Outbound links" section of the Cell Links: Consolidate or Maintain Consistency of Data page it specifically mentions "outbound links must be removed one at a time". We did exactly as the job aid specifies (moused over the cell, used the "Delete" button). We expected just this one cross-sheet reference, "{ALLOCATIONS project hub}" to be deleted and as such as were absolutely prepared for any formulas in the sheet linking to this column to produce errors.

Upon navigating back to said sheet it was discovered that essentially ALL formulas were producing #INVALID REF errors. Digging in we noticed that not just this one cross-sheet reference but instead ALL cross-sheet references from the same sheet were deleted - 18 in total. These were 18 unique references, each to a different column within the same sheet.

We ended up manually opening up each formula and re-linking the cross-sheet references one by one.

In others' experiences, is this the expected behaviour for deleting an outbound link? Or should it only delete the link for the cell/column/reference in question? Thanks in advance.

Paul Newcome

I would think that it should have only deleted that particular range reference. Because the range can be linked across multiple cells via similar (or very different) formulas, I would expect it to "break" all of the other formulas with that specific range listed, but not all other ranges as well.

 

To explain in a different way, if you enter a cell that has a formula containing a cross sheet reference, then click to set the courser within that range name, it gives you the option to edit the reference. 

 

When you edit that reference, it will automatically update all occurrences of that reference within the sheet.

 

I would think that deleting a cross sheet reference would work the same way. It would basically remove that particular reference across all instances within the sheet, but it would not affect other DIFFERENT cross sheet references.

JLC

Thanks Paul, that was what I expected the outcome to be as well. Was sorely disappointed to see otherwise. FYI to anybody reading!

Paul Newcome

And I just want to make sure I am clear on what happened (not exact details but the general idea)...

You have two cross sheet references

{Reference Sheet Range 1}

{Reference Sheet Range 2}

When you deleted {Reference Sheet Range 2}, {Reference Sheet Range 1} was automatically deleted as well?

Paul Newcome

What steps did you take exactly to delete the reference again...?

 

Did you delete from the source sheet, or did you delete from the sheet containing the formulas?

JLC

Hi Paul, for sure --

Yes, you are correct. Except the ranges were 1 through 18  

As per your other comment -- I deleted from the source sheet, NOT the sheet where the formulas were housed. Clicked on one cell of one of the referred-to columns, and used the "delete" button in the blue window.

Paul Newcome

I feel like this may be an expected (but not necessarily desired) behavior. When I tested deleting from the source sheet, I got the attached pop-up warning.

 

To delete individual references, you would need to delete from the formula sheet.

JLC

Hey Paul, definitely seems to be the expected behaviour - but agreed, not desired! Was totally expecting only the one ref to delete based on the fact that I was actually in one ref/column when hitting the delete button, as well as the verbiage on SS's job aid for this scenario. It did seem when I called in that there was confusion amongst support on what the behaviour should be. Hopefully this post saves at least one other person from a headache