The two linked workbook files must be compiled into the same EXE. Otherwise, this won’t work. Or you can compile one EXE and keep the other workbook external (unprotected) and open it programmatically with VBA, so that VLookup works.
The Master file has all ingredients listed. It has a matrix where different suppliers’ prices are listed and it pulls the cheapest supplier through. Lots of coding is involved.
The Cost Calculator file is for costing of Finished Products for different customers. (the Cost Calculator is saved per customer and has many recipes per customer. Each recipe is on its own sheet in the workbook and pulls through to one sheet where the overhead costs, bulk costs and packaging costs are calculated).
To do this, it needs to pull the Master Data through to get the ingredient prices.
I cannot put Master Data on the Cost Calculator file, as the end user would have to update every customer’s workbook manually, which defeats the whole purpose of the program.
The sole solution would be relying on a third unprotected workbook file where you store the data to be shared between the two workbooks. Unfortunately, this can involve a lot of modifications to your existing system.