Referencing File names in Excel...

[demtro]

Hi everybody,

I have an excel spreadsheet that pulls data from 2 other spreadsheets. I am using Vlookup for most of the information. It is working fine, the problem i am having is that the spreadsheet is an export from a sharepoint site and the sharepoint site gets updated with new information everyday...

I tried exporting the data to a new spreadsheet with the same name and overwriting it, however the formulas no longer work because (I assume) excel actually references the SID and not the file name...

I need to know if there is any work around for this. I need to be able to pull data from a specific spreadsheet, that will be overwritten on a daily basis...any way to tell excel to use a filename instaed of the SID?

thanks
Demtro

 

[Jerry Sullivan]

Hi Demtro and Welcome,

I tried exporting the data to a new spreadsheet with the same name and overwriting it, however the formulas no longer work because (I assume) excel actually references the SID and not the file name...

You should be able to overwrite you data source workbook with a file of the same sheetnames and column layouts and have your Vlookup formulas read the new file data.

If your workbook with the formulas is already open when you overwrite the source data file, you'll need to update the values for the links.

Data > Edit Links > Update Formulas
(or just close and reopen your workbook with the formulas).