Locking cell positions to another cell if one column is using IMPORTRANGE

Question

Let's say I have a master list of all kinds of Legos and their cost. Only two columns. Column A is the Lego type and column B is the cost for that Lego. Example: 3x3 thin block and 1. or small wheel and 3. Now I want to make a separate sheet, let's call it car which imports column A and B from the master list with the addition of a column C that has the number of pieces that are needed to build the car, like "4 wheels". A Column D is the cost for each line item and will have a total at the bottom.

IMPORTRANGE allows me to pull A and B into the car sheet. I add numbers into C column for all the parts needed for that Lego car and the D column will give me a total for the cost of the car. To make a helicopter sheet, I just make a blank template from the car sheet and put in numbers into C column that build a helicopter.

This all works fine until I add new Lego pieces into the master list. The A and B columns for the Lego projects (the car and helicopter) will update but the C (and therefore D) column will be wrong on the car and heli sheets. Everything will be shifted when I added things to the A and B column.

What do I do?

Ideally, on each project sheet I have this happen. This is not actual code, just a thought. I add a new Lego to master list and the templated project sheets respond by:

If (trigger that column A has updated)  {  
get.rowNum;  
//the row num where the part was added on master 
//sheet and on this sheet via importrange   

Insert.cell (column C, rowNum);  
//because the A and B column will already insert a  
//whole row in those columns. I don't want to insert   
//another whole row. But instead only insert a single  
//cell into column C on the same row number as where  
//the a and b updated.
}  

This allows to keep it constantly alphabetized and all the sheets auto add new parts without shifting my count columns.

Here's a link to a demo https://drive.google.com/folderview?id=0By-rLiXHjfqRc01QZmRISGprbm8&usp=sharing

Answer 1

This question looks to be an example of the X-Y problem (see What is the XY problem?) because the OP is asking about how to implement a figured out solution rather than how to solve the problem.

The scenario
Having a master sheet with a list of parts that will be updated with new parts, update the model sheets.

1. On the master sheet add a column for each part counts by model
2. On the sheets by model use IMPORTRANGE to import the data from the master sheet and QUERY to show only the required columns.

Benefits

• The main benefit is that the above approach will keep the spreadsheets without scripts.
• Another benefit of this approach is that most of the data entry could be done in one place.