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!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

kkellyc

New Member
Joined
Sep 28, 2006
Messages
2
GENIUS!!! How many times have I been in that dialog and not noticed that check box? Wow, thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,809
Messages
5,544,415
Members
410,609
Latest member
agarci1096
Top