Cross workbook references & File Folders

Occasional Visitor

Hello,

I am using a VLookup formula in an invoice template that refers to a price list (source doc for the link) within a separate workbook. The price list, the invoice template, and the completed invoices were all within the same location and everything was fine.

 

The link began updating incorrectly (I don't want the link to update at all) after I put the price list and the invoice templates within 1 folder ("Invoice Templates"), and the completed invoices into a different folder ("X Customer Name).

 

The issue now is that whenever I create a new invoice using the template, then drag that completed invoice into a customer folder, the Vlookup 'table array' input changes. Everything is fine until I move the invoice into the customer folder.

Within the template, the table array refers to the Price List file in the correct location. After dragging the completed invoice into the customer folder, the Vlookup function updates, and thinks that the Price List file is located within the customer folder, which it isn't.

 

1. Original formula in template: Price List file location-(Server\data\invoice templates)

Mikel1310_0-1648755543297.png

 

2. Create invoice using template.

 

3. Move saved invoice into Folder: (Server\data\Invoices\customer name)

 

4. Formula within invoice now looks like:

Mikel1310_1-1648757734498.png

 

Why is the file location for the Price List automatically updating when I move the invoices? Is there a way to prevent this?

 

 

 

0 Replies