external references that should be internal

**marnie** · 03-01-2007, 10:43 AM

I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).

In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.

BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.

I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.

Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.

One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?

**oldchippy** · 03-01-2007, 11:01 AM

When you have the file open, try this, go to Edit > Links and look at the options there

**marnie** · 03-01-2007, 03:36 PM

It lists only the external references. Is there something specific I should be looking for when the dialog box opens?

Thanks
marnie

**oldchippy** · 03-01-2007, 05:03 PM

I was thinking may be you had some phantom links?

http://j-walk.com/ss/excel/usertips/tip054.htm

**marnie** · 03-02-2007, 08:08 AM

Thanks Oldchippy,

I know how to find all my external links, what I'm trying to figure out is how internal links can spontaneously become external pointing to another file after 2 years.

Here's what the internal link looked like for 2 years

=+Input!N39+Input!AP39

Every month I'd update the file with new data and this reference would pick up the updated data just like it should.

Then after 2 years the formula suddenly changed to this

=+'T:\FY07\October\[oct final.xls]Input'!N39+'T:\FY07\October\[oct final.xls]Input'!AP39

Since I never go back and revise older month's, this October file would never get updated. So when I tried updating for November data, it was still picking up October's. It appeared to only happen in 1 tab, 1 column, but multiple cells just like this.

The rest of the file maintained all internal and external references as I put them in.

This scenario only happened to me once before. And now I find out, it's happened to others in my office and nobody knows what causes it.

It worries me because I can't be constantly checking to see if I have more external references than I intended.

Thanks again for your attention

**oldchippy** · 03-02-2007, 08:52 AM

Having not had a problem like this myself, it difficult to understand why this should happen?

When you say Every month I'd update the file with new data and this reference would pick up the updated data just like it should is it only yourself that does the update? And are you absolutely sure updated data just like it should?

**marnie** · 03-02-2007, 02:25 PM

The answer to your first question is unfortuately many others use this file. Everyone is aware of the problem. We're all trying to figure it out. Being that it was a couple of months before I discovered it, I don't know who or when the change actually occurred. As I mentioned in my 1st post, we use various version so of Excel. I have 2000, I don't know what the other versions. One of the times this problem happened, it occurred in a file that I never used. It happened to my coworker. I don't know what version he has.

The answer to your 2nd question is, I update this tab the same way every month. This part of the file is typically only updated by me and 1 other person. Here's what happens each month.

The reference picks up 2 cells from the other tab. The cells are sum formulas picking up 12 cells in their respective lines. The 12 cells are for each month. In July, I paste values the July $ amount into the July column. When August comes around, I paste values the August $ amt into the August column and the sum formula picks up the update. The other tab (where my problem occurred) reads the cells with the =sum formula. It's so simple, it's hard to imagine how this could happen.

We do a lot of pasting values into this file (and many others). We never had a problem with this before. I don't know if it's related.

Thanks again
Marnie

**Bryan Hessey** · 03-02-2007, 05:52 PM

Originally Posted by marnie

The answer to your first question is unfortuately many others use this file. Everyone is aware of the problem. We're all trying to figure it out. Being that it was a couple of months before I discovered it, I don't know who or when the change actually occurred. As I mentioned in my 1st post, we use various version so of Excel. I have 2000, I don't know what the other versions. One of the times this problem happened, it occurred in a file that I never used. It happened to my coworker. I don't know what version he has.

The answer to your 2nd question is, I update this tab the same way every month. This part of the file is typically only updated by me and 1 other person. Here's what happens each month.

The reference picks up 2 cells from the other tab. The cells are sum formulas picking up 12 cells in their respective lines. The 12 cells are for each month. In July, I paste values the July $ amount into the July column. When August comes around, I paste values the August $ amt into the August column and the sum formula picks up the update. The other tab (where my problem occurred) reads the cells with the =sum formula. It's so simple, it's hard to imagine how this could happen.

We do a lot of pasting values into this file (and many others). We never had a problem with this before. I don't know if it's related.

Thanks again
Marnie

Hi Marnie and oldchippy,

it looks as though Excel has needed to identify its focus, perhaps a mixture of formula and Copy/Paste with two sheets named 'Input' being referenced concurrently, ie, you have formula that refers to Input! in this book, and you Copy from Input in another book and Paste into this book, then you select the Input sheet for this book (with the 'Copy' still active in the other book), or something similar.

Having this error I presume that you do not press ESC after pasting to clear the 'Copy' but allow that to self-clear.

Have you reset the links to see at what point it might happen again?
to reset links, press
CTRL ~
(to show formula)
then Edit, Replace (or Ctrl/H)
and put 'T:\FY07\October\[oct final.xls]Input'!
as the Find, and Input!
as the replace, and replace All.

CTRL ~ again to reset the view.

Hope this helps, and look forward to further comments.
---

**oldchippy** · 03-02-2007, 05:59 PM

Thanks Bryan for your input here, your suggestion sounds quite reasonable why this is happening, hope it cures the problem

**Bryan Hessey** · 03-02-2007, 06:13 PM

Originally Posted by oldchippy

Thanks Bryan for your input here, your suggestion sounds quite reasonable why this is happening, hope it cures the problem

Hi oldchippy,
the only time I have noticed this 'clarification' is if you download someone's 'sample' and play with some of the formula, the reference will switch from Internal to External, which, in an opened xls from a .zip in one of MS wonderful 'temporary' areas, just about blows the character limit for a formula

My only other thought was that 'Input' looks very close to being a reserved word, (or one about which Excel might get confused) but I don't think that is the cause here.

Cheers.
---

**marnie** · 03-05-2007, 08:33 AM

Thanks to both of you. To answer the one question, yes, we've reset the formulas using the find/replace function.

I think you might be right. We may just be confusing Excel by opening multiple files with the same references. I often work in 1 file and open the previous month to do some comparisons. I always open the previous month as read only just to be safe. But the current month file might need to identify the proper location. Also, my boss often opens many of these files at the same time and makes changes. We are a hospital system with 5 hospitals. To maintain these files for all 5 hospitals, he'll often open all 5 at the same time to make revisions and be sure he keeps them consistent.

Although, I don't know that I can prevent this from happening again.

Thanks again for all your attention.

**Bryan Hessey** · 03-05-2007, 09:56 AM

Originally Posted by marnie

Thanks to both of you. To answer the one question, yes, we've reset the formulas using the find/replace function.

I think you might be right. We may just be confusing Excel by opening multiple files with the same references. I often work in 1 file and open the previous month to do some comparisons. I always open the previous month as read only just to be safe. But the current month file might need to identify the proper location. Also, my boss often opens many of these files at the same time and makes changes. We are a hospital system with 5 hospitals. To maintain these files for all 5 hospitals, he'll often open all 5 at the same time to make revisions and be sure he keeps them consistent.

Although, I don't know that I can prevent this from happening again.

Thanks again for all your attention.

Hi,

as such you probably can't, different sheetnames could be a problem with formula (which could be overcome by Indirect, but at what cost).

A Macro with Shortcut key to reset the links is probably the way to go.

hth
---

external references that should be internal

LinkBack

Thread Tools

Rate This Thread

Display

external references that should be internal

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions