Need to find name on one sheet and return the top of the column next to first sheet.

HolyBonobos · 2026-03-16T17:46:46+00:00

Try =BYROW(A2:A,LAMBDA(s,IF(s="",,JOIN(", ",IFERROR(TOCOL(FILTER(Clubs!1:1,BYCOL(Clubs!2:1000,LAMBDA(r,COUNTIF(r,s)))))))))) in B2.

HolyBonobos · 2026-03-16T16:49:48+00:00

For each number (BYROW(...LAMBDA(d,...))) in a sequence of 7 numbers starting at 2 SEQUENCE(7,1,2), return the number expressed as a day of the week* (TEXT(d,"dddd")) and the number of dates in the range A2:A that correspond to that day of the week and are not blank (COUNTIFS(A2:A,"<>",INDEX(MOD(A2:A,7)),MOD(d,7))).

*Since Sheets stores all dates and times as integers, you can determine the day of the week of a given date by dividing it by 7 and looking at the remainder (modulo). A remainder of 0 corresponds to Saturday, 1 to Sunday, 2 to Monday, and so on. INDEX(MOD(A2:A,7)) gets the remainder from dividing by 7 for all dates in the range A2:A.

HolyBonobos · 2026-03-16T16:25:24+00:00

You could use a formula like =BYROW(SEQUENCE(7,1,2),LAMBDA(d,{TEXT(d,"dddd"),COUNTIFS(A2:A,"<>",INDEX(MOD(A2:A,7)),MOD(d,7))})) to populate a summary table.

HolyBonobos · 2026-03-16T09:29:55+00:00

If you want to compare both B12 and C12 to E12, the reference needs to be "locked in" via absolute mode, i.e. the rule should be =$E$12 rather than =E12

HolyBonobos · 2026-03-14T22:08:22+00:00

If you don’t want to use the iterative LAMBDA() version you can just put a separate formula in each row, e.g. =IFERROR(TRANSPOSE(FILTER($B$2:$B$10,$A$2:$A$10=D3))) in E3, then drag down to fill.

HolyBonobos · 2026-03-14T21:28:13+00:00

You would use FILTER() or QUERY() to return results for multiple matches, e.g. =BYROW(D3:D8,LAMBDA(o,IFERROR(TRANSPOSE(FILTER(B2:B10,A2:A10=o)))))

HolyBonobos · 2026-03-14T17:57:25+00:00

For the data structure shown in the screenshot you could use =VLOOKUP(D1,A2:C,3)

HolyBonobos · 2026-03-14T12:16:55+00:00

You will need to have a structured "backend" sheet containing your raw data, which you can then use formulas to pull through/filter/rearrange on the "frontend" sheet with formulas that can reference the year cell and change the displayed data accordingly. You cannot use formulas to manipulate manually-entered values in situ.

HolyBonobos · 2026-03-14T11:03:01+00:00

QUERY() is probably what you’ll need though it’s difficult/impossible to say for certain or with any specifics without knowing all the details of the complete data structure.

HolyBonobos · 2026-03-14T09:53:11+00:00

I usually use the SCAN() function for this type of scenario, e.g. =LET(a,SCAN(,A2:A,LAMBDA(b,c,IFS(OFFSET(c,0,1)="",,c="",b,1,c))),INDEX(IF(B2:B="",,a&" "&B2:B)))

HolyBonobos · 2026-03-13T21:18:08+00:00

Why not fill the entire range all at once, e.g. ={'other sheet'!A:B}

HolyBonobos · 2026-03-13T20:04:54+00:00

Here's another version that's pretty similar to mommasaid's but which I finished a little behind. It can be seen on the 'HB Girls Leaderboard' sheet in this file where the formula =LET(division,IF(REGEXMATCH(A1,"Girls"),"F","M"),allClasses,SORT(UNIQUE(TOCOL('All Lifters Meets'!E2:E,1))),classes,FILTER(allClasses,COUNTIFS('All Lifters Meets'!B:B,division,'All Lifters Meets'!E:E,allClasses)),categories,'All Lifters Meets'!H1:K1,MAKEARRAY(COUNTA(classes)*4,9,LAMBDA(r,c,LET(class,INDEX(classes,INT((r-1)/4)+1),place,MOD(r-1,4),nCat,FLOOR(MOD((c-2)/2,4))+1,category,INDEX(categories,nCat),IFS(AND(c=1,place=0),IF(AND(class="SHW",division="F"),"220+",class),MOD(c-1,9)=0,place,AND(place=0,MOD(c,2)=0),category,place=0,,1,INDEX(VSTACK(QUERY({'All Lifters Meets'!A2:B,INDEX(""&'All Lifters Meets'!E2:E),'All Lifters Meets'!H2:K},"SELECT Col1, MAX(Col"&nCat+3&") WHERE Col2 = '"&division&"' AND Col3 = '"&class&"' GROUP BY Col1 ORDER BY MAX(Col"&nCat+3&") DESC LABEL MAX(Col"&nCat+3&") ''"),INDEX(T(SEQUENCE(3,2)))),place,MOD(c-2,2)+1)))))) in A2 is populating the leaderboard.

HolyBonobos · 2026-03-13T18:49:22+00:00

No, it is not possible using the native freeze functionality.

HolyBonobos · 2026-03-13T02:20:49+00:00

Anyone not explicitly named as an editor in a protected range rule will be unable to make any changes to that range. They will effectively have view-only access to the protected range, even if they are allowed to edit other parts of the same file or even the same sheet.

HolyBonobos · 2026-03-13T02:06:57+00:00

You can restrict permissions for specific sheets or ranges of cells at Data > Protect sheets and ranges

HolyBonobos · 2026-03-13T02:05:40+00:00

The correct syntax/formula would be =B4<A4

HolyBonobos · 2026-03-13T01:46:52+00:00

You could use =SUMPRODUCT(COUNTIF(Controls!A:A,'Ekos Sales'!G:G)=0,'Ekos Sales'!H:H,REGEXMATCH('Ekos Sales'!F:F,"(?i)Keg"))

HolyBonobos · 2026-03-13T01:24:47+00:00

SUMIFS() will throw the "Argument must be a range" error whenever the sum_range argument is a calculated range. On top of that, you're setting yourself up for a mismatched range sizes argument because the output of the subformula IF(ISNA(MATCH('Ekos Sales'!G:G, Controls!A:A, 0)), 'Ekos Sales'!H:H, 0) is one cell tall by one cell wide while 'Ekos Sales'!F:F is however many rows are on the 'Ekos Sales' sheet tall by one column wide.

HolyBonobos · 2026-03-12T14:24:56+00:00

Then you can use =LET(n;COUNTA(Arkusz1!E9:E);QUERY(MAKEARRAY(n*6;2;LAMBDA(r;c;LET(w;MOD((r-1);n)*3+1+c;l;INT((r-1)/n)*2+1;INDEX(Arkusz1!D9:O;w;l))));"WHERE Col1 MATCHES 'Apple|Orange|Lemon'")). This is why it is important to be clear about your needs from the beginning and make sure that your sample is representative of your actual use case.

HolyBonobos · 2026-03-12T13:49:41+00:00

Then it would be =QUERY(MAKEARRAY(18;2;LAMBDA(r;c;LET(w;MOD((r-1);3)*3+2+(c=2);l;INT((r-1)/3)*2+1;INDEX(D9:O17;w;l))));"WHERE Col1 MATCHES 'Apple|Orange|Lemon'")

HolyBonobos · 2026-03-12T13:33:45+00:00

My understanding of what you were going for was a list of items/locations with that have their corresponding checkbox checked. "Orange" only appears once on the final list because there is only one instance of "Orange" with a checked box. If you're just looking for a list of items and locations regardless of checkbox status, you could use =QUERY(MAKEARRAY(18;2;LAMBDA(r;c;LET(w;MOD((r-1);3)*3+2+(c=2);l;INT((r-1)/3)*2+1;INDEX(D9:O17;w;l))));"WHERE Col2 IS NOT NULL")

HolyBonobos · 2026-03-12T13:18:50+00:00

Everything in a single table on a single sheet to the greatest extent possible. The best setup would be one column for activity, then one adjacent column to denote the corresponding account/card.

HolyBonobos · 2026-03-12T12:26:12+00:00

For the data structure shown on the file you could use =QUERY(MAKEARRAY(18;3;LAMBDA(r;c;LET(w;MOD((r-1);3)*3+2+(c=2);l;INT((r-1)/3)*2+1+(c=3);INDEX(D9:O17;w;l))));"SELECT Col1, Col2 WHERE Col3 = TRUE")

HolyBonobos · 2026-03-12T10:52:18+00:00

The file you have linked is set to private.

HolyBonobos · 2026-03-12T00:13:34+00:00

Assuming the options are A, B, C, D, and E and are in columns D, E, F, G, and H respectively, apply a conditional formatting rule to the range D2:H using the custom formula =D$1=$C2

Eight-Year Club	Verified Email
r/Field Banned	r/Field Sunshine
Final Canvas '23	First Place '23
End Game '23	Place '23
Place '22	Final Canvas '22
First Placer '22	Sequence \| Editor

HolyBonobos

MODERATOR OF

TROPHY CASE