google-sheetsvlookupflattengantt-chartgoogle-query-language
I add the charts manually and not using any formula. I put the name of the role and color a row in weeks that he was active.
Now I want to do some resource management calculations for every input like "Backend" role and fill a table of information like this:
google sheet - search for value in 2 dimension array
I am creating a Gantt Chart in Google sheet. This what it looks like:
I add the charts manually and not using any formula. I put the name of the role and color a row in weeks that he was active.
Now I want to do some resource management calculations for every input like "Backend" role and fill a table of information like this:
was "Backend" active in month number 1 ? yes
How many weeks in month 1, "Backend" was active? 3
The address of "Backend's" start week: C6
The address of "Backend's" end week: E6
Can I do that with Formulas and not custom functions? Here is the link to my sheet.
Solution
try:
=ARRAYFORMULA(IFNA(VLOOKUP(C22:C25,
QUERY(QUERY(IFERROR(SPLIT(FLATTEN(IF(B3:M15="backend", IF(B2:M2="",,
HLOOKUP(COLUMN(B1:M1), IF(B1:M1<>"", {COLUMN(B1:M1); B1:M1}), 2, 1))&"×"&1, )), "×")),
"select Col1,'yes',sum(Col2) where Col1 is not null group by Col1"),
"offset 1", 0), {2,3}, 0), {"no", 0}))
