How to make excel stop putting a hyperlink in my lookup formula after exiting the file

[lionhearted012]

I made a calculator that grabs data from an external online reference. On the first sheet I have a link that is =HYPERLINK("http://www.wowuction.com/us/illidan/horde/Tools/RealmDataExportGetFileStatic?type=csv&dl=true&token=5L7U_r_iGWpMkMqHHmfPgQ2","Refresh") refresh is just the name it appears as. I then have sheet 2 which is a calculation sheet and sheet 3 which has a bunch of item names, id's and prices. sheet 2 grabs all of the information from sheet 3, runs it through the calculations i set then posts all the results on sheet 1 for which items the person should buy for profit. sheet 3 pulls the data from an external excel spreadsheet B that always has the same name but i cannot edit it for whatever reason and spreadsheet B is the spreadsheet that opens up every time the hyperlink on sheet 1 from spreadsheet A is clicked. The information from spreadsheet B is updated every hour from an external website.

Here is the problem when I close spreadsheet A, excel edits my lookup formula on spreadsheet A sheet 3, which causes my lookup formula to not work anymore. What it does is it posts the hyperlink address from sheet 1 into the formula and I have no idea why. Here is an example of what the broken formula looks like =LOOKUP(I7,'http://www.wowuction.com/us/illidan/horde/Tools/[RealmDataExportGetFileStatic?type=csv&dl=true&token=5L7U_r_iGWpMkMqHHmfPgQ2]RealmDataExportGetFileStatic'!$E:$E,'http://www.wowuction.com/us/illidan/horde/Tools/[RealmDataExportGetFileStatic?type=csv&dl=true&token=5L7U_r_iGWpMkMqHHmfPgQ2]RealmDataExportGetFileStatic'!$J:$J)

here is what the formula looks like before excel edits it when i close the file =LOOKUP(I7,'RealmDataExportGetFileStatic'!$E:$E,'RealmDataExportGetFileStatic'!$J:$J)

How do i make excel stop putting the hyperlink reference from the sheet 1 into the lookup formula on sheet 3?

 

[Andrew Poulsom]

If you reference an open external workbook in a formula, the formula will be amended to include its full path when you close it. That's also the case if you open the target workbook when the source workbook is closed. That's what's happening in your scenario. The solution is to move sheet RealmDataExportGetFileStatic into your workbook when the workbook that contains it is opened.