3

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

  • 1
    This looks like a pretty fragile setup. Apparently, without ImportRange, the "car" sheet just has some numbers without any indication of what pieces they refer to? This is a recipe for all kinds of errors. There should be a column that identifies pieces somehow, by their name, Id Number, whatever. This column should be present in both sheets, so that it can be used to query one sheet from another, pulling the data for specific pieces (not "looks like it's in this row, so it's probably for this piece"). – user79865 Oct 27 '15 at 5:19
  • The car sheet has column A and B from the master sheet using ImportRange. These columns are the unique Id's that the car sheet imports. To clarify, on the car sheet row 1 has: Wheel , 3, 4, 12. Under the column names: Lego part name, cost per part, number of parts used, total cost – allindal Oct 29 '15 at 0:31
  • 1
    But what's in importrange is a separate data set, which may well be edited independently of the car sheet. The problem is that on its own, the car sheet has only "4, 12" in columns C and D. If the car sheet knew on its own what these numbers refer to, it would be able to request specific items from the master sheet, without relying on the rows being in same positions in two different sheets. – user79865 Oct 29 '15 at 0:52
  • These sheets are more dynamic then that solution. There will be dozens and downs of projects. If I add new parts to the master sheet, I need them to show up on every project automatically without shifting my counts. That's why the project sheets can't request specific part data from the master sheet. I want users to see all avaliable parts on every project sheet so they can update the recupe without having to make new data request links to the master sheet. – allindal Oct 30 '15 at 5:58
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.