Forum Discussion
Links to named cells in external files
We use external links to different files for calculation. The cells in the external files are referenced by a cell name.
Sometimes, there are several cells with the same name in multiple tab sheets. The link includes the path to the selected tab sheet. After saving the file with the links, closing and reopening it, some links "forget" the tab sheet part of the path and use the one cell, which name is available in all tab sheets in the source file.
Is there a way to prevent this "forgetting" of the tab sheet part?
Why does excel forget the tab sheet part?
2 Replies
You wrote:
Is there a way to prevent this "forgetting" of the tab sheet part?
To which the answer is "Yes."
Why does excel forget the tab sheet part?
Hard to say. But given that there have been (as of this writing) 50 views of your question and no one else has ventured to answer, let me begin by asking a question or two.
- What's the overall design of this set of files? [This is a very fundamental question!]
- What purpose is served by having so many sheets, sheets that clearly have some redundancy among them? [redundancy apparent if only through the repeated use of the same names for some cells]
- To what extent are you open to considering redesign of the overall system?
Another way to prevent your problem that occurs to me is to rename one or more of the redundant names, perhaps incorporating the sheet/tab/cellname into one name.
Do you use INDIRECT to make the references? There might also be ways to use INDIRECT to incorporate sheet & tab & cellname into a single reference that is unambiguous.
But more fundamentally, it might make sense to simplify the overall organization, which is why I started there.
