Excel 2007 : Tying cells to another automatically populated spreadsheet

I apologize if I missed a thread already asking this question. I searched for a while but couldn't find anything quite like what I'm trying to do.

Scenario:
I have 5 spreadsheets; four of which are tables with part numbers, quantities, and a forecast spreading over 12 months. There are cells in each of these 4 tables that are tied to a corresponding cell with that part number in the 5th spreadsheet. These values are the quantities on hand and quantities on order. These cells are protected and automatically update with the 5th spreadsheet. I made this workbook because it used to be that we had to manually look up these quantities in our Linux system for each part number. This way, they will automatically fill in when opening the workbook.

The 5th spreadsheet is a list generated with external data using the "Data -> From Other Sources -> Microsoft Query -> Linux Data" connection. This list gets part numbers added to it fairly often because of the data entered into our Linux system; which of course means that rows are added to the list. But, this external data consists of part number, quantity on hand, quantity on order, and quantity on backorder columns for each part number.

Problem:
To tie each cells to the 5th spreadsheet, I am using the formula:

Please Login or Register  to view this content.

This worked fantastically for months because no parts were being added to the Linux system for this specific window of parts I am dealing with. But, when a part was added to Linux and another row was automatically added to the external data list, all my indirect references were shifted down a row and the 4 spreadsheets lost their correct cell connections to the 5th.

Is there another function to get these connections to change with the changing external data spreadsheet when it updates?

Please tell me if my example is unclear. I hope I described it well enough.
Thanks in advance for the help.

Assuming that you have a key field, for example, a product code, which is unique, you might be better using a VLOOKUP or an INDEX/MATCH to search for the value(s) rather than assuming they're going to stay in the same place.

Regards

I'm sorry to reopen this thread, but I'm stuck once again.

I followed your advice and tried using VLOOKUP and INDEX/MATCH to do what I needed. However, I was not getting the results I wanted and actually could not solve a run-time error. So, looking around, I thought that a better option would be using a FIND function. I found a handy script on another help forum and tried to integrate it. But, when the code is executed, it loops on a cell until another "run-time error -2147417848 (80010108)". I have posted the Excel file to better explain the situation. I have taken off the connection to the external data and simply pasted the table as an example. In the actual spreadsheet, the data in the "Inventory" sheet is automatically updated when the file is opened.

Please, go easy on my VBA coding. I'm far from adept at this.

I would really appreciate it if someone could look at the code in my spreadsheet and see if they can see anything wrong.

Toro Planning Schedule.xlsm