Absolute References - Linking Workbook Files

[Galto]

Hi,

This is a roundabout way to my question, but here goes...

I'm working with two spreadsheets and want to link several cells between the two...

e.g.
In cell A1, on the file called "DESTINATION", I have a formula like this..

='[SOURCE.XLS]Sheet1'!$A$200

So whatever value in SOURCE, Cell A220 will show up in cell A1 of DESTINATION.... simplest of simple, right?

By default, the cell reference is absolute (with the $'s in place) - I didn't immediately understand why. However, I wanted to have the same link to a range of cells starting with A200 and going down and right - 13 columns x 11 rows. Paste Special > Formulas doesn't work because of that absolute reference. So, I removed the $'s in the cell A1 formula and copied formulas, and all worked well.

I then closed the DESTINATION file and made some formatting changes to the SOURCE file - including the removal of some rows. Upon opening DESTINATION, all my references were wrong because of the removal of those rows.

So, I now understand that I need to leave those $'s in the formulas, but is there a way to copy a range of cells with absolute references?.... other than individually entering each formula? Over 1500 cells in all!

Thanks!

 

[Joel Horowitz]

I am not sure I understand your problem. However, let me help you.

1) I think that Excel links are updated when the source is modified (e.g. removed rows) ONLY if they are in a file that is open. So in your case, if you modify anything to your source file, you should leave the destination file open. That should solve your problem. By the way, it's quite normal that Excel works this way: otherwise it would have to constantly track all the files linking to your active worksheet, or otherwise track all the rows added/removed during the life of the worksheet.

2) besides this, FYI, absolute references do not behave differently when rows are inserted/removed in the source file. The only instance where an absolute reference behaves differently from a relative is when you copy it somewhere else.

Hope this helps

Joel

 

[Galto]

Thanks Joel,

I understand your response - I guess my question was more related to creating several absolute references at once - a range of cells. The copy and paste didn't work well.

There are times when a quick edit will need to be done on the source file. The good news is that once the source file format is established, it will be locked down and protected from the "regular users" who may feel the need to make a "helpful" change :wink:

In the meantime I may have found a solution. I tried naming the range of cells in the source file, and referring to that range name on the destination file. In my first tries, it seemed to work. Just gotta run it through a few more tests.

Thanks again.