title: Google sheet inventory quantity
body: <p>I have an inventory spreadsheet "Inventory" that has 3 sheets. Inventory List, Parts Used, On Order. And has a form to submit what parts were used to the Parts Used sheet. There is also another spreadsheet "Parts Received" that has a form to submit data when parts are received.</p>
  
  <p>I am trying to</p>
  
  <p>Reference the Part Number from the Parts Used sheet "column E" (each time a form is submitted) to the Inventory List sheet part number "column D". And Subtract the quantity on the Parts Used sheet "column H" from the Inventory List sheet quantity "column H".</p>
  
  <p>So each time the parts used form is submitted to the parts used sheet, it deducts that quantity from the Inventory list sheet, using the part number as a reference to know what two quantities to subtract.
  I have tried getting this code to work but with no luck</p>
  
  <pre><code>function updateInventoryFromPartsUsed() {
      var partsused = {}; // A mapping of partNumber =&gt; quantity.
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('Parts Used');
      if (sheet) {
          // For each row, if column "E" == "stock" then set partNumber, quantity.
          partsused[partNumber] = quantity;
      }
      // Now partsused should look like {'SL249':5, 'ML50':1, 'MWF':1}
      sheet = ss.getSheetByName('Inventory');
      if (sheet) {
          // For each row, set quantity, partNumber.
          var purchased = purchaseOrders[partNumber];
          // Set column "A" value = quantity + purchased
      }
  }    
  </code></pre>
  
  <p>Have also tried different formulas such as: 1. VLOOKUP(B23,$A$2:$B$5, 2, TRUE) to compare data. This did not work either</p>
  
  <p>I am having problems with this. If someone could help I would really appreciate it</p>
  
  <p>Thanks</p>
  
  <p>Bryan</p>
  
  <p><strong>Someone else has solved this</strong> (will post the answer here, if it might help any one):</p>
  
  <p>By adding two more columns to the right of H 'Inventory List' - Adding these:</p>
  
  <p>I3 =arrayformula(if(row(A3:A)=3,"Parts Used",if(len(D3:D),sumif('Parts Used '!E6:E8,D3:D,'Parts Used '!H6:H),)))</p>
  
  <p>J3 =arrayformula(if(row(A3:A)=3,"Parts Remaining",if(len(D3:D),H3:H-I3:I,)))</p>
  
  <p>They also did this. Added a condition format to 'parts used' </p>
  
  <p>A3:K34 is =isna(match($E3,indirect("Inventory List!D3:D"),0))*len($E3)</p>
  
  <p>This will highlight a row on 'Parts Used' when a part number does not match one on the 'Inventory List'. Helps you see if someone entered a part number incorrectly or if the part needs added to the inventory list. </p>
  
  <p>The solution they gave worked great, very helpful.</p>
  
  <p>Thanks for taking the time to read the question</p>