Google Sheets Query / Array

Question

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

Does anyone have access to that original doc? I know Matt King was involved.
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

where in which sheet you want that arrayformula and how should the output of that arrayformula look like? — player0
– player0, Commented Nov 19, 2020 at 16:28
I think the spreadsheet is missing a tab which has a simple inventory of all items. The output should be a simple count. — Matteo Cossu
– Matteo Cossu, Commented Nov 19, 2020 at 19:29
@player0 would be happy to talk about a project if you can help. — Matteo Cossu
– Matteo Cossu, Commented Nov 19, 2020 at 20:34
I am free, tho still not sure what are you after. can you create a new sheet in your spreadsheet and show some sample how do you wish data to be outputed? — player0
– player0, Commented Nov 19, 2020 at 20:38
My goal is to populate column I of the Inventory Sheet, from values in BOM Import total quantities, and decrease when values of Starter Kits Requested column D is set to Fulfilled @player0 — Matteo Cossu
– Matteo Cossu, Commented Nov 19, 2020 at 21:37

player0 · Accepted Answer · 2020-11-19 21:57:29Z

2

={"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)))}

={"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))))}

edited Nov 19, 2020 at 21:57

answered Nov 19, 2020 at 21:38

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Sheets Query / Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related