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!
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!