Externally referenced data somehow traveling with file

kkellyc

New Member
Joined
Sep 28, 2006
Messages
2
This falls under the category of seriously unexpected program behavior.

I have a very simple file which references data externally within some Index functions. A value in the top-left corner of the sheet contains a Row_num value used by all the Index functions, and the referenced ranges are in an entirely separate workbook (simply columns of data in the separate workbook). I intended to use this to pull information pertaining to one student (one row in the separate workbook) into a small workbook that could then be sent to that student.

Theoretically, if the receiving student does not update external links, they will see their information, and if they do update links, the formulas will return errors...since they do not have a copy of the externally referenced workbook, right? I would also expect that changing the row_num value would trigger a re-calc which would trigger an attempt to update links as well.

Well, what actually happens is the student can change the row_num value, the index functions redirect to the specified rows in their externally referenced ranges, and the little workbook which contains only external references refreshes to display all the information for a different student!

Mind boggling.

Has anyone seen this? I am thinking that the index function must actually pull the entire contents of a referenced external range into the file itself, even storing it with the file, to speed up access. I would love to find some documentation confirming or denying this, however.

Thanks!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi Kelly

I suspect it may be to do with your workbook options: under Tools>Options>Calculation tab and towards the bottom of the dialog under Workbook options, do you have "Save external link values" checked? This will save down a copy of the external link values that will, I believe, travel around with your workbook, thus resulting in the behaviour you are seeing.

Best regards

Richard
 
Upvote 0
GENIUS!!! How many times have I been in that dialog and not noticed that check box? Wow, thanks.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top