Absolute References - Linking Workbook Files

Galto

New Member
Joined
Mar 11, 2002
Messages
31
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joel Horowitz

New Member
Joined
Aug 23, 2002
Messages
34
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

New Member
Joined
Mar 11, 2002
Messages
31
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.
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top