I have a (destination) workbook that through a defined name (DefinedName) references a range in another (source) workbook. The definition for the defined name is
I have put a link (edit links) in the destination workbook for SourceWorkBookName so that I can change the version number or location of the source workbook without changing the many formulas that reference the external range.
I also have a definition by the same name (DefinedName) in the destination workbook. It is just
because it points to a range in its own workbook.
The nature of the application is that the range in the source and its definition gets lengthen by VBA macros as follows:
etc.
However, the definition in the destination workbook is not automatically similarly updated. This causes references from the destination workbook to the new rows to fail. As a stopgap, I’ve just chosen to make the definition in the destination workbook arbitrarily large. $s$500 for instance. However eventually, the source definition grows to exceed this, and I’m left a year or so later, having forgotten how all this works, with trying to debug this. (The situation right now.)
So with this scenario, how am I really supposed to design/implement this? I could have the VBA macro update the definition in both workbooks (it does only the “source workbook” now). However, there must be some mechanism that already exists to get both definitions in sync or to have only one definition that serves both? Using VBA to do the updates is problematic because there are multiple destination workbooks at different times that reference the same source workbook.
Thanks in advance for any advice.
Bookmarks