CODEWITHSUNDEEP
google-sheetsarray-formulas
Matteo Cossu
Matteo Cossu

Reputation: 11

Google Sheets Query / Array

I'm trying to build a very simple inventory sheet. Apologies in advance, but I'm very new at this.

a) I'd like my inventory sheet to show stock for individual items, and for kits (BOMs) b) I'd also like the array formula to update depending on an adjacent column value (i.e. if the row shows 'fulfilled' then the array should ignore it.

I have been able to modify an old post that I found here, but unfortunately the original document was modified. https://support.google.com/docs/thread/23230245?hl=en

  1. Does anyone have access to that original doc? I know Matt King was involved.
  2. Does anyone know of a beginner's guide to Google Sheets Query / Array formulas?

I know I'm speaking in gibberish, but believe me I'm fascinated with this and I'm a quick learner. Would also be interested in paid coaching if anyone's into that!

https://docs.google.com/spreadsheets/d/1btnxxI00qI9njIUzudAZXh4G87TWcHoWtfPl4jBl7jM/edit?usp=sharing

Upvotes: 0

Views: 126

Answers (1)

player0
player0

Reputation: 1

={"Units"; ArrayFormula(IFNA(VLOOKUP(B2:B, QUERY({'BOM Import'!F2:I},
 "select Col1,sum(Col4) 
  where Col4 is not null
  group by Col1 
  label sum(Col4)''"), 2, 0)))}

enter image description here

={"Total Stock on Hand"; ArrayFormula(IF("yes"=IFNA(VLOOKUP(IFNA(VLOOKUP(B2:B, {'BOM Import'!F2:F, 'BOM Import'!E2:E}, 2, 0)), SORTN(SORT({'Kits Requested'!B2:B, 'Kits Requested'!A2:D}, 2, 0), 9^9, 2, 3, 1), 5, 0)), 0, 
 
 IFNA(VLOOKUP(B2:B, QUERY({'BOM Import'!F2:J}, "select Col1,sum(Col5) where Col5 is not null group by Col1 label sum(Col5)''"), 2, 0))))}

enter image description here

Upvotes: 2

Related Questions