The Most Basic Task Not Working - Referencing another Spreadsheet

To me I must be doing something so obvious I cannot see it...



I have two spreadsheets:

(A) - Price list - Master AV Price List (Nov 2023) its only one worksheet ... Each widget on this spreadsheet has 9 columns of data that reference attributes of the widget so: Description, Part Number, Local Price, USD Price, Date of Entry etc.



Also on this same worksheet I have referenced 2 columns (on the far right) from another spreadsheet that is a Sort?* Order of all the widgets - this never changes, but allows me to add new widgets (new rows) throughout the worksheet (Price list) and this referenced 2 columns expands/contracts without inserting a row within...



(B) - Estimating Sheet (Set up as an Excel template) this is made up of Multiple tabs reflecting different costs estimates for different Room Types - Each tab has locally inputted data (widget-rows) that makes up the bill of materials, then each line references Spreadsheet (A) to obtain part numbers and local price.?* (To obtain the cell value from Spreadsheet (A) I am using the formula "=" Spreadsheet (A) cell reference. eg:?*='[MASTER AV PRICE LIST (Nov2023).xlsx]Sheet1'!$G$422



This all works as expected. HOWEVER... When I add additional widgets (rows) to Spreadsheet (A) some of my part numbers and local pricing that was anchored from Spreadsheet (A) change on Spreadsheet (B)!! - This is really doing my head in and I cannot figure it out - any help would be gratefully appreciated.

See attachment across two tabs

I think you should be using a FILTER or XLOOKUP function rather than a simple cell reference. However, that might not work unless the reference workbook is open. You'd need to test that.