Say that a co-worker writes a VLOOKUP to a closed workbook and refers to $A$1:$S$99,19 to grab a value from column S. The co-worker sends you the linking workbook but not the linked workbook. Perhaps there is some confidential information in columns B through R of that workbook.
Everyone involved might be initially surprised that you can take the linking workbook to a new computer, without access to the closed workbook on the original computer, and drag out the entire table stored in A1:S99 of the closed workbook, including the confidential bits.
I will admit I was initially surprised, but I guess I shouldn't be surprised. Everyone should be aware of this, and think before you set up wide VLOOKUPs to closed workbooks.
Everyone involved might be initially surprised that you can take the linking workbook to a new computer, without access to the closed workbook on the original computer, and drag out the entire table stored in A1:S99 of the closed workbook, including the confidential bits.
I will admit I was initially surprised, but I guess I shouldn't be surprised. Everyone should be aware of this, and think before you set up wide VLOOKUPs to closed workbooks.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1774 - Are Links to Closed External Workbooks Cached in the Workbook?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm passing along a trick I learned at the Excel SoCal meetup back in July, and this came from, I'm almost sure was Ryan.
Ryan right here said that he had a job delivering pizzas at night, and he would set up the laptop and just put on the excelisfun, or the bjele123 channel at YouTube, and learned a lot about Excel.
Has a great job now as an Excel data analyst, and stumbled upon this one recently, and it took me a couple of weeks to actually come back and try it, so.
Right now you're watching not my usual podcast computer, you're watching my desktop computer, because we want to use two different computers here.
I have EmployeeTable.xlsm , that's stored on this desktop computer, it has ID, base pay bonus pay, and name.
And I'm going to CTRL+TAB over to our podcast file, Podcast1774.
This is some other workbook, and I want to set up an external link to that other table, so =VLOOKUP this value, comma, and I'll CTRL+TAB back to this table, set up the link, and of course we don't have to press f4 here, that's automatic, it puts the dollar signs in, and went ,4,FALSE , and it gets us the answer.
And of course, you know, that's an external link, it works all the time, no surprises or hassles there, right.
And even here, I'm going to switch back and we'll change Ike2 to Ike.
I'll save this workbook, I'll close this workbook, and even though the workbook is closed, it continues to work like we expect.
Right?
Nice!
So I have this file, it's stored here in this computer.
I'm going to save it out to the Dropbox, and we're going to revisit on the other computer.
Alright hey, welcome back, I'm over here on the podcast computer.
So here's our Dropbox Podcast1774, 54 seconds ago, and we're going to open this in Excel.
Now it says "Hey, automatic update of links has been disabled", I'm not going to enable that content, we're on another computer here, and it continues to work.
So if I do 111, you know, it's getting the other information from that closed workbook, and that closed workbook is not on this computer, and these two computers are not networked.
So there's really no path back to that file, and in fact this C:\aaa , there is an "aaa" folder on this computer, but it doesn't have an EmployeeTable at all.
So, you know, somehow, miraculously here, Excel is allowing this external link to work, and that is as it's supposed to work!
It's one of those things that if you have less than 10,000 cells in a closed Excel file, the links continue to work, even if you send the links to someone else.
But here's what Ryan has discovered: I want to go into this formula, and I'm going to very carefully select everything after the comma, up through the A3 like that, CTRL+C.
Now remember the original employee table, I'm just getting employee name here, but there was other confidential stuff in there, like salary, base pay, and bonus, and things like that.
So I copied that from the clipboard, press Esc, and then come down here, I'll type = and CTRL+V, and I want to point to A3, but I'm not going to put the $, I'm going to take those out.
So we want to create a nice relative reference there, pointing to that closed workbook.
And when I press Enter it says "Well hey, you got to go find that workbook", but I'll just click Cancel, and then return that value from A3.
That's interesting, CTRL+C, and we will paste about the size of the table, CTRL+V, and again they asked me to go find the file, and I just click Cancel there, returning the entire table.
Alright, that's kind of crazy, I guess it shouldn't surprise me that it works, clearly there has to be something on here that this VLOOKUP would continue to work, but being able to extract the whole VLOOKUP table including these confidential columns here, I guess, surprises me just a little bit.
So.
I'm passing this along just that way you're aware, if you're using links to external workbooks, and there's stuff in the middle, like you know, you're not referring to this in the VLOOKUP table that's confidential, you don't want people to see it.
You need to think twice about that, because it would be possible for someone on the other side to extract that information.
Alright hey, an addendum here, you know, I guess it's not really any mystery at all.
I'm going to rename this xlsm file to be a .zip, and then open the zip file, go to \xl\externalLinks\externalLink1.xml , open that up.
Of course, in order to make the the external link work, they would have to send along the values they last had before I close out of their workbook on the other computer.
And it's, as I look here A3 through A3, all the way down, they're only sending the values that were linked.
So again, as I said in the in the call out there to the right recently, if you would make sure just the link to columns A and B, put ID in A and name and B, and not includes the salary or bonus in the linked area, then that information would not get sent along.
So surprising, yeah I was surprised, I never thought about it.
But of course, it would have to be in here in order for it to work.
Well hey, I want to thank Ryan for passing that cool tip along, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
If you're a fan of Excel videos, and of course if you're watching this you are, check out the "Excel Daily News" at paper or exceldailynews.com, it's a collection of all the videos and blog posts about Excel in the last 24 hours!
Learn Excel from MrExcel podcast, episode 1774 - Are Links to Closed External Workbooks Cached in the Workbook?
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm passing along a trick I learned at the Excel SoCal meetup back in July, and this came from, I'm almost sure was Ryan.
Ryan right here said that he had a job delivering pizzas at night, and he would set up the laptop and just put on the excelisfun, or the bjele123 channel at YouTube, and learned a lot about Excel.
Has a great job now as an Excel data analyst, and stumbled upon this one recently, and it took me a couple of weeks to actually come back and try it, so.
Right now you're watching not my usual podcast computer, you're watching my desktop computer, because we want to use two different computers here.
I have EmployeeTable.xlsm , that's stored on this desktop computer, it has ID, base pay bonus pay, and name.
And I'm going to CTRL+TAB over to our podcast file, Podcast1774.
This is some other workbook, and I want to set up an external link to that other table, so =VLOOKUP this value, comma, and I'll CTRL+TAB back to this table, set up the link, and of course we don't have to press f4 here, that's automatic, it puts the dollar signs in, and went ,4,FALSE , and it gets us the answer.
And of course, you know, that's an external link, it works all the time, no surprises or hassles there, right.
And even here, I'm going to switch back and we'll change Ike2 to Ike.
I'll save this workbook, I'll close this workbook, and even though the workbook is closed, it continues to work like we expect.
Right?
Nice!
So I have this file, it's stored here in this computer.
I'm going to save it out to the Dropbox, and we're going to revisit on the other computer.
Alright hey, welcome back, I'm over here on the podcast computer.
So here's our Dropbox Podcast1774, 54 seconds ago, and we're going to open this in Excel.
Now it says "Hey, automatic update of links has been disabled", I'm not going to enable that content, we're on another computer here, and it continues to work.
So if I do 111, you know, it's getting the other information from that closed workbook, and that closed workbook is not on this computer, and these two computers are not networked.
So there's really no path back to that file, and in fact this C:\aaa , there is an "aaa" folder on this computer, but it doesn't have an EmployeeTable at all.
So, you know, somehow, miraculously here, Excel is allowing this external link to work, and that is as it's supposed to work!
It's one of those things that if you have less than 10,000 cells in a closed Excel file, the links continue to work, even if you send the links to someone else.
But here's what Ryan has discovered: I want to go into this formula, and I'm going to very carefully select everything after the comma, up through the A3 like that, CTRL+C.
Now remember the original employee table, I'm just getting employee name here, but there was other confidential stuff in there, like salary, base pay, and bonus, and things like that.
So I copied that from the clipboard, press Esc, and then come down here, I'll type = and CTRL+V, and I want to point to A3, but I'm not going to put the $, I'm going to take those out.
So we want to create a nice relative reference there, pointing to that closed workbook.
And when I press Enter it says "Well hey, you got to go find that workbook", but I'll just click Cancel, and then return that value from A3.
That's interesting, CTRL+C, and we will paste about the size of the table, CTRL+V, and again they asked me to go find the file, and I just click Cancel there, returning the entire table.
Alright, that's kind of crazy, I guess it shouldn't surprise me that it works, clearly there has to be something on here that this VLOOKUP would continue to work, but being able to extract the whole VLOOKUP table including these confidential columns here, I guess, surprises me just a little bit.
So.
I'm passing this along just that way you're aware, if you're using links to external workbooks, and there's stuff in the middle, like you know, you're not referring to this in the VLOOKUP table that's confidential, you don't want people to see it.
You need to think twice about that, because it would be possible for someone on the other side to extract that information.
Alright hey, an addendum here, you know, I guess it's not really any mystery at all.
I'm going to rename this xlsm file to be a .zip, and then open the zip file, go to \xl\externalLinks\externalLink1.xml , open that up.
Of course, in order to make the the external link work, they would have to send along the values they last had before I close out of their workbook on the other computer.
And it's, as I look here A3 through A3, all the way down, they're only sending the values that were linked.
So again, as I said in the in the call out there to the right recently, if you would make sure just the link to columns A and B, put ID in A and name and B, and not includes the salary or bonus in the linked area, then that information would not get sent along.
So surprising, yeah I was surprised, I never thought about it.
But of course, it would have to be in here in order for it to work.
Well hey, I want to thank Ryan for passing that cool tip along, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
If you're a fan of Excel videos, and of course if you're watching this you are, check out the "Excel Daily News" at paper or exceldailynews.com, it's a collection of all the videos and blog posts about Excel in the last 24 hours!
