Google sheets ArrayFormula with several pages

Question

I made an example - https://docs.google.com/spreadsheets/d/1xRl1vBttWToJeDjJB4Okd6FKidLyvoPF2uXJmLCOzPI/edit?usp=sharing

Formula Used:

=Arrayformula(INDEX(FILTER(POIOS!D2:D;POIOS!B2:B<=B2;POIOS!C2:C=A2);COUNTA(FILTER(POIOS!D2:D;POIOS!B2:B<=B2;POIOS!C2:C=A2))))

In the table on the sheet, the results are filtered by values from the next two sheets. Filtered by week number and name. When I add the Arrayformula, I expect that when adding data to the first sheet, the calculations will automatically appear in a new row. But this does not happen. What am I doing wrong?

I looked at examples of using the Arrayformula, but they are only for simple tables on one sheet.

I noticed that there is a COUNTA in the original formula, should there be a count in the output instead of enumerating the items? — PatrickdC
– PatrickdC, Commented Oct 11, 2024 at 14:47

PatrickdC · Accepted Answer · 2024-10-11 14:55:46Z

0

Use Lambda Function as an Alternative

You may use this instead:

=BYROW(A2:B3;LAMBDA(x;HSTACK(
JOIN("; ";FILTER(POIOS!D1:D6;POIOS!B1:B6<=INDEX(x;;2);POIOS!C1:C6=INDEX(x;;1)));
JOIN("; ";FILTER(Stages!D1:D6;Stages!B1:B6<=INDEX(x;;2);Stages!C1:C6=INDEX(x;;1))))))

A couple of join formulas were added to take into account multiple entries. I also noticed the <= in the original formula so it was also applied to the new formula.

Output:

References:

edited Oct 11, 2024 at 14:55

answered Oct 11, 2024 at 14:43

PatrickdC

2,5733 gold badges10 silver badges30 bronze badges

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

4 Comments

PatrickdC Over a year ago

Please feel free to ask anything with respect to the answer posted above.

Doleensk Over a year ago

Thanks for the reply. I need to get exactly the last value by week number. I don't need all the values. I have the correct formula. I just need it to automatically pull in when adding data to columns A and B on the Results sheet.

PatrickdC Over a year ago

Can you add an expected input/output so that I may adjust the formula I provided?

Doleensk Over a year ago

Thanks for the answer, but I only need the arrayformula to drag down when adding rows. So that I don't have to drag manually. The arrayformula should automatically fill in the cells with formulas. I added a sheet RESULTS BYROW and placed your formula on it. When adding a row, the formula was not added to column C

Collectives™ on Stack Overflow

Google sheets ArrayFormula with several pages

1 Answer 1

Use Lambda Function as an Alternative

Output:

References:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Use Lambda Function as an Alternative

Output:

References:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related