In Brief – Excel links to cells in other Workbooks occasionally change the WORKSHEET reference within the link formula. Why?
In Detail
We use an Excel template for estimating purposes. A typical project has 10 to 30 estimate files.
Each of these Estimating files has a MASTER worksheet and a couple dozen BREAKOUT worksheets (BREAKOUT 1, BREAKOUT 2, etc).
The Master worksheet and the Breakout worksheets are identical in structure and there are many common ‘local’ Named Ranges (cells) in use.
We have a Summary template that will pull approx. 30 values from each of the Estimate files (one row per Estimate file). Every cell references has a Named Range.
Therefore, the Summary file for a project could have 300 to 900 links in it. I do know that links can be ‘taxing’ on Excel.
We always place the Summary file in the same folder as the Estimate files.
The links in the summary file are constructed from the Path, File name, Worksheet name, Cell reference (Named Range).
Eg =@’https://myco.sharepoint.com/Sh Doc/Sales/Projects/Some Company/Quote No & What it is/Est/Rev 2/[File Name]Worksheet Name’!Cell’s Named Range
Note that this is a SharePoint path (and has been for 4 years now). Worksheet Name should always be “MASTER”.
Estimate files will be worked on for a couple weeks typically. During this time, files are opened, changed, closed constantly.
From observation, we’ve noted that Excel will “abbreviate” the link formula when the Estimate file is open. The path is dropped.
The formula collapses to =@’[File Name]Worksheet Name’!Cell’s Named Range **if** the cell is a local Named Range.
The formula collapses to =@’File Name’!Cell’s Named Range **if** the cell is a global Named Range.
If the file is closed, the full – path/file name/worksheet name/cell – does return.
In addition, I should point out that we work with these files thru a OneDrive Sync to the SharePoint files.
Now, here’s where the weird behavior occasionally rears its head.
Every now and again, we’ll get a row in the Summary file where some of the link formulas show that the worksheet name MASTER has changed to BREAKOUT 1.
The link is still valid since both Worksheets have the same Named Range on them. The value of that cell though can be wildly different.
The links that connect to a cell with a global Named Range always work. I have not observed those ever changing.
It seems that somewhere along the way, Excel loses track of the Worksheet name, then goes looking for the Named Range but since it is not a global name, it finds the first occurrence of the local name which happens to be on BREAKOUT 1 (alphabetically). At least, that’s the only sense I can make as to why it is that particular Worksheet that it changes to. Eg – If my MASTER Worksheet name was called AMASTER, would I have ever seen this problem?
I’m wondering if others have seen this behavior. For us, it is rare, but can be devastating if not caught. For the moment, I’ve built some error checks into my Summary file.
I’m not sure, but I don’t think this happened before we moved our files to SharePoint.
Is the SharePoint – OneDrive sync the issue?
Is this a Microsoft glitch?
Anything else that could trigger this? Eg – Memory resources tapped out.
I know that there are a couple of easy fixes I could apply, but my challenge is maintaining backwards compatibility. We reuse files that can be a decade old or more. I haven’t had time to investigate this very much yet. One attempt to change the cell name on the MASTER worksheet from being a local name to a global name caused a minor blowup.
Looking forward to comments, insight, experiences etc that people can share.
In Detail
We use an Excel template for estimating purposes. A typical project has 10 to 30 estimate files.
Each of these Estimating files has a MASTER worksheet and a couple dozen BREAKOUT worksheets (BREAKOUT 1, BREAKOUT 2, etc).
The Master worksheet and the Breakout worksheets are identical in structure and there are many common ‘local’ Named Ranges (cells) in use.
We have a Summary template that will pull approx. 30 values from each of the Estimate files (one row per Estimate file). Every cell references has a Named Range.
Therefore, the Summary file for a project could have 300 to 900 links in it. I do know that links can be ‘taxing’ on Excel.
We always place the Summary file in the same folder as the Estimate files.
The links in the summary file are constructed from the Path, File name, Worksheet name, Cell reference (Named Range).
Eg =@’https://myco.sharepoint.com/Sh Doc/Sales/Projects/Some Company/Quote No & What it is/Est/Rev 2/[File Name]Worksheet Name’!Cell’s Named Range
Note that this is a SharePoint path (and has been for 4 years now). Worksheet Name should always be “MASTER”.
Estimate files will be worked on for a couple weeks typically. During this time, files are opened, changed, closed constantly.
From observation, we’ve noted that Excel will “abbreviate” the link formula when the Estimate file is open. The path is dropped.
The formula collapses to =@’[File Name]Worksheet Name’!Cell’s Named Range **if** the cell is a local Named Range.
The formula collapses to =@’File Name’!Cell’s Named Range **if** the cell is a global Named Range.
If the file is closed, the full – path/file name/worksheet name/cell – does return.
In addition, I should point out that we work with these files thru a OneDrive Sync to the SharePoint files.
Now, here’s where the weird behavior occasionally rears its head.
Every now and again, we’ll get a row in the Summary file where some of the link formulas show that the worksheet name MASTER has changed to BREAKOUT 1.
The link is still valid since both Worksheets have the same Named Range on them. The value of that cell though can be wildly different.
The links that connect to a cell with a global Named Range always work. I have not observed those ever changing.
It seems that somewhere along the way, Excel loses track of the Worksheet name, then goes looking for the Named Range but since it is not a global name, it finds the first occurrence of the local name which happens to be on BREAKOUT 1 (alphabetically). At least, that’s the only sense I can make as to why it is that particular Worksheet that it changes to. Eg – If my MASTER Worksheet name was called AMASTER, would I have ever seen this problem?
I’m wondering if others have seen this behavior. For us, it is rare, but can be devastating if not caught. For the moment, I’ve built some error checks into my Summary file.
I’m not sure, but I don’t think this happened before we moved our files to SharePoint.
Is the SharePoint – OneDrive sync the issue?
Is this a Microsoft glitch?
Anything else that could trigger this? Eg – Memory resources tapped out.
I know that there are a couple of easy fixes I could apply, but my challenge is maintaining backwards compatibility. We reuse files that can be a decade old or more. I haven’t had time to investigate this very much yet. One attempt to change the cell name on the MASTER worksheet from being a local name to a global name caused a minor blowup.
Looking forward to comments, insight, experiences etc that people can share.
