Smartly Linking Workbooks

[amit_megiddo]

Hi..

I have one master Workbook with many different sheets. I want each sheet to feed a separate workbook which will update automatically based on my updates to the master workbook.
Copying as a link doesn't work because of all the zeros (and I can't just ignore zeros because they are actual values).
SO what I did was an If function, if the equivalent cell in master workbook is blank then blank and if not then its content.

The problem is that I want to insert rows and columns to the master and see them on the clone (I need an EXACT clone).

I tried doing something with offset:
=IF([Workbook1.xlsm]Sheet1!A2="","",OFFSET(INDIRECT([Workbook1.xlsm]Sheet1!A2),ROWS($A$1:A2)-1,COLUMNS($A$1:A2)-1))
But it didn't work.

Please Help,
Thank you!

[6StringJazzer]

Welcome to the Forum amit_megiddo!

When describing your problem, a good explanation includes a description of what behavior, error code, or error message you are getting. "It didn't work" can mean lots of things.

I only know of two ways to refer to data in a closed external workbook in a way that allows insertions and deletions in the external workbook. One is an add-in that provides a function called INDIRECT.EXT. You can find a download link and instructions at http://www.ashishmathur.com/tag/indirect-ext/. The second way is to use VBA to populate and refresh hard-coded external references, or just outright copy the data. (There may be other ways that I don't know about.)

Meantime, here is why you're having a problem. In your formula above, you are using INDIRECT incorrectly. INDIRECT takes a string that is interpreted as a range reference. However, you are giving it an actual range reference. For example, suppose I have a file name in cell B2 and I want to refer to that file, Sheet1, cell A1:

Please Login or Register  to view this content.

An INDIRECT function that refers to an external workbook will return an error if the external workbook is not open, so this probably is not the solution you want.

The formula below would be a better fit for your problem but that also requires the external workbook to be open.

Please Login or Register  to view this content.

If you want to clone another cell and protect against blank cells, another method that is an alternative to using an IF is

Please Login or Register  to view this content.

[amit_megiddo]

Hey...

Thanks for the reply.
I really want it to be so that the clone of the master doesn't have to be open at all times for it to update.
What would be the VBA solution?

Thanks.

[6StringJazzer]

I have done a little experimenting with this (I haven't used external references the same way you need to) and determined that if both books are open, any insertions and deletions in the source book are treated in the clone as if they were in the same book. That is, if you insert a row in the source book, the clone book's formulas will update so that a given row keeps referencing the same data, not the same row. The clone won't show the inserted row. However, if the clone book is closed when the source book is updated, then the source book is closed, then the clone book is opened, the formulas in the clone are unchanged and still refer to the same rows, and show the insertion.

More simply, if you never have both books open at the same time, my last formula above will work. I suspect you did your testing with both books open, which caused the problems you had.

If your scenario requires you to have both books open at least sometimes when the source is adding or removing rows, then I can put together some VBA code that refreshes the data in the clone each time it is opened.

[amit_megiddo]

Hi again and thanks for the quick replies.
I tried what you said with one book being closed and it worked! Awesome..
However, I do need to be able to make sure things are updated even if their both open.
The reason being is that I have my master WB which I update and then I have different people working/viewing their cloned books and I can't control when they open them and when the close them. It will be highly disruptive to our workflow.
So if you have the time to work out a solution.. much much appreictaed!