Inserting new information in source document that can update destination documents

I am setting up a construction cost file to be the source to be referenced by multiple cost estimating files for various model homes I intend to offer to build as a contractor. The intention is to be able to update the cost file with current prices that will be referenced by the destination files.

I have discovered that the default of cell reference for an external file is absolute. When I insert new line with new information, this result in the reference to the wrong line. Experimenting with blank files, I see that if the external references are not absolute, the destination file will track the changed location as does the information in relative references to cells within a file. With both files open, if relative reference is =[Book1.xlsx]Sheet1!B6 without the $ to make absolute reference, the destination cell's formula will track the new location. However, if the destination file is closed when I make the insertion, it will not.

I am looking for tips on how to update the source construction cost file so that it will by reference update the destination files that reference it for cost. If I do not insert new lines or fields, this works. However, to keep information organized, I need to insert information where it is logical. Since many destination files are going to reference the source files, having them all open is not practical.

Great tip on the Vlookup function. I have been making it work.

However, screen save shows an example of it not working. I have entered
=H60*VLOOKUP(F60,'[Material Cost.xlsx]Openings'!$A$12:$H$16,1,FALSE)
H60-quantity to multiply vlookup value
F60-the cell reference to text to look up value
'[Material Cost.xlsx]Openings'-external file named "Material Cost.xlsx", sheet named "Openings",
$A$12:$H$16-on that sheet table defined from cell A12 to H16 with absolute $ reference
3-# of colume to enter value
FALSE- Exact Reference
Yet I get the dreaded #/N/A when I enter the formula. Help again will be appreciated.
VLOOKUP Problem.jpg

Thank you for previous tip about this feature.

Eli