Why Do My VLOOKUPs Keep Changing?

[ransomedbyfire]

For some reason, every now and then, my VLookups that reference cells in another workbook will change from referencing just "Filename.xls" to the whole long name of the file, beginning with C:\. Since I am making these spreadsheets for use by other people, this is very annoying. Any help is greatly appreciated!

 

[mikerickson]

The switch from workbook name to file name would indicate that a workbook has been closed.

 

[ransomedbyfire]

Oh. That's interesting. What do you suppose would be the best fix then? Or should I consider moving the contents of that workbook to another sheet in the workbook that contains the VLookups?

 

[T. Valko]

Oh. That's interesting. What do you suppose would be the best fix then? Or should I consider moving the contents of that workbook to another sheet in the workbook that contains the VLookups?

There's nothing to fix. That just how Excel works. If you link to or reference an external file Excel includes the path to that file in formulas. If the file happens to be closed then the path is a lot longer.

It's a good idea to keep as much of your data as possible in a single file.

 

[bjurney]

Not sure why its a problem unless you running out of characters in your formula, which 2003 you can use 1024 characters. You can add this to the My Workbook section so the external file opens when you open the workbook with the formulas in it

Private Sub Workbook_Open()
Workbooks.Open Filename:="C:\My_file.xls"
End Sub

 

[ransomedbyfire]

The reason I think it might be a problem is if I give the spreadsheet to one of my clients and it tries to reference something on my hard drive (i.e. under my name as a user) not theirs. Also, when the reference changed, it was changing my columns reference of "B:D" to "REF!". So,, I went ahead and put the sheet in the same workbook as the vlookups.

 

[T. Valko]

The reason I think it might be a problem is if I give the spreadsheet to one of my clients and it tries to reference something on my hard drive (i.e. under my name as a user) not theirs. Also, when the reference changed, it was changing my columns reference of "B:D" to "REF!". So,, I went ahead and put the sheet in the same workbook as the vlookups.

Yes, that's a good thing to do!