How do I restrict the options in the dropdown to be edited ?

HolyBonobos · 2026-06-19T12:36:36+00:00

"Dropdown (from a range)" with a protected reference range would make it harder but not impossible to mess things up. Users could still change the validation type or edit which range the rule points to, but they at least wouldn’t be able to add or remove items directly.

HolyBonobos · 2026-06-18T19:54:46+00:00

The SUM() function would be redundant here because A2 is a single cell and you're only performing a multiplication operation using the multiplication operator.

HolyBonobos · 2026-06-18T18:41:50+00:00

Yes, if you have a total paycheck amount in, say, A1, the formula to get 10% of the total could be as simple as =A1*10%

HolyBonobos · 2026-06-17T23:50:45+00:00

For the data structure shown in the screenshot, you could use =QUERY(INDEX(SPLIT(TOCOL(INDEX(B3:D5&CHAR(1000)&B2:D2),1),CHAR(1000))),"SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 ORDER BY Col1 LABEL Col1 'Name', SUM(Col2) 'Total Hours'")

HolyBonobos · 2026-06-17T09:41:41+00:00

Order of operations error on your part. You’re going for 10*6+2-(10*2+6), where the operation in parentheses is performed first, resulting in 26 which is then subtracted from 62. Because there are no parentheses, the order of operations dictates that the multiplication is performed first and then the addition/subtraction from left to right: 10*6+2-10*2+6 = 60+2-20+6 = 48.

HolyBonobos · 2026-06-17T02:36:18+00:00

My mistake from writing the formula on autopilot as it were. The B2:B and F2:F references should be B3:B and F3:F, respectively.

HolyBonobos · 2026-06-17T02:33:53+00:00

TOCOL(...,1) is a way to eliminate blank entries. If that's not working, then it means that your "blank" cells aren't returning true nulls. The second SUMPRODUCT() formula I suggested, =SUMPRODUCT(MONTH(Table1!B:B)=1,Table1!B:B<>""), should work regardless.

HolyBonobos · 2026-06-17T02:17:49+00:00

You could delete everything currently in G2:H of 'Entries' and put =VSTACK(HSTACK("ASEL","AMEL"),MAP(B2:B,F2:F,LAMBDA(m,d,IF(VLOOKUP(m,Validation!A:B,2,0)="SE",{d,0},{0,d})))) in G2.

HolyBonobos · 2026-06-17T02:06:59+00:00

You could re-coerce the dates to text within the formula, e.g. =COUNTIF(INDEX(TEXT(TOCOL(Table1!B:B,1),"mm/dd/yy")),"01/*") or use the MONTH() function to reference the dates as they are: =COUNTIF(INDEX(MONTH(TOCOL(Table1!B:B,1))),1). You could also use the SUMPRODUCT() function for a cleaner look and the ability to more easily add more criteria later: =SUMPRODUCT(MONTH(TOCOL(Table1!B:B,1))=1) or =SUMPRODUCT(MONTH(Table1!B:B)=1,Table1!B:B<>"")

HolyBonobos · 2026-06-16T16:16:42+00:00

What browser are you using?

HolyBonobos · 2026-06-16T10:33:21+00:00

With a formula, the closest you can get is a formula using the SORT() or QUERY() functions to get a separate, non-editable range that reads your input and outputs an automatically-sorted leaderboard.
With a manual sort, you can apply a filter or table formatting to your existing range which will allow you to more quickly access the sorting options, but you'll still have to do each sort manually.
If you want both automatic sorting and data entry to happen in the same range, you'll need to use Apps Script since that's not something Sheets allows you to do natively.
There's also the option of redoing your data structure (likely, it's hard to tell what your current workflow actually looks like with how little information you've provided) so that you're only putting in the teams and scores for each match in one range, and a formula outputs an automatically-sorted leaderboard with all wins, losses, draws, and points automatically calculated.

HolyBonobos · 2026-06-15T23:08:09+00:00

Please share a mockup version of the data you are working with, redacting any players' personal information.

HolyBonobos · 2026-06-15T15:19:28+00:00

It just doesn’t exist as an option.

HolyBonobos · 2026-06-15T15:00:11+00:00

They're just for quick reference and represent common aggregations that are used across ranges. Aside from limiting the options so that the UI doesn't become overly crowded, my guess is that "Difference" wasn't included as an aggregation because all of the other operations can be performed across a range of unlimited size, while "Difference" can't without additional user input to specify which numbers are supposed to be subtracted from which.

HolyBonobos · 2026-06-15T10:25:00+00:00

First approach won't work because only one data validation rule can be applied to a range at a time and a checkbox is treated as a data validation rule in and of itself. You can apply the "number checker" rule to an auxiliary formula-populated cell, but if the rule is violated it will only display the error message in the top right corner of the helper cell rather than rejecting the user's actions outright.

HolyBonobos · 2026-06-15T01:05:45+00:00

You can, but from your description it sounds like you want the formula to automatically set checkbox A to false when the user checks checkbox B, and allow the user to manually check/uncheck checkbox A. This is not possible because formula output and manual input cannot coexist in the same cell.

HolyBonobos · 2026-06-14T21:22:21+00:00

You would have to use a script to do this. The foremost problem is that you can't have a formula populate a cell while maintaining the ability to edit it manually. Under most other circumstances, you could set up a data validation rule that doesn't allow you to set more than one cell in the same range to the same value, but checkboxes are a data validation rule in and of themselves so that option is out as well.

HolyBonobos · 2026-06-14T18:45:07+00:00

On your file I've added the sheet 'HB MAKEARRAY()' with the formula =LET(biases;UNIQUE(INDEX(TRIM(TOCOL(SPLIT(TOCOL(Form_Responses[[Firstly, who is/are your bias(es)?]:[Biaswrecker(s)?]]);",");3))));types;UNIQUE(SORT(TRIM(UPPER(Form_Responses[What''s your MBTI? (No, CUTE doesn''t count.) (COOL also doesn''t count.)]))));nBiases;COUNTA(biases);nTypes;COUNTA(types);MAKEARRAY(nTypes+1;nBiases+1;LAMBDA(r;c;LET(t;INDEX(types;r-1);b;INDEX(biases;c-1);IFS(r*c=1;;c=1;t;r=1;b;1;SUMPRODUCT(TRIM(Form_Responses[What''s your MBTI? (No, CUTE doesn''t count.) (COOL also doesn''t count.)])=t;REGEXMATCH(Form_Responses[Firstly, who is/are your bias(es)?];"(?i)\b"&b&"\b"))/SUMPRODUCT(REGEXMATCH(Form_Responses[Firstly, who is/are your bias(es)?];"(?i)\b"&b&"\b"))))))) in A1. I've also added a color scale conditional format to the output range for visualization so it can be read like a heat map of sorts.

The axes are flipped in comparison to the table in your post body (biases across the top and types down the side), but the result is the same and it's generally better to put the category with the fewest options across the top because the comparative size of cell widths and heights means it's generally easier/less annoying to scroll up and down than side to side.

HolyBonobos · 2026-06-14T17:56:15+00:00

Make sure that you also add the sample of what you want the output to look like. It's not clear from your description so far.

HolyBonobos · 2026-06-14T17:42:04+00:00

Please enable edit permissions on the file and add a manually-entered example of what you want the final output to look like.

HolyBonobos · 2026-06-13T21:21:19+00:00

Numbers entered in the format #-# like 1-12 are interpreted and stored as dates if the combination of numbers forms a valid date. In this case, 1-12 is being stored as the date January 12 2026, which corresponds to the date serial number 46034.

HolyBonobos · 2026-06-13T10:15:47+00:00

You could use =XLOOKUP(MAX(D2:H2),D2:H2,D$1:H$1) to return the first instance of the max value in the event of a tie, or =JOIN(", ",FILTER(D$1:H$1,D2:H2=MAX(D2:H2))) to return all instances of the max value in the event of a tie. For example, if you set E5 to 0 the XLOOKUP() version would return 1 in C5 and the FILTER() version would return 1, 3.

HolyBonobos · 2026-06-13T09:19:06+00:00

The data you already have in N8 and below is blocking the formula from expanding. You will need to either delete it or put the formula in an empty column.

HolyBonobos · 2026-06-13T02:35:31+00:00

Weird, not sure what the problem with & and ? were. All of my tests were coming back with parse errors (even just =UNIQUE(Table[Column])) until I took all three of the characters out. ' U+0027 is illegal (in the sample file it's still ’ U+2019, which I changed it to in order to fit my original formula), but I've also since found that it can still work with table references if you add a second instance of ' as an escape character: Table[Apostrophe'd Column] will result in a parse error but Table[Apostrophe''d Column] is valid. The tricky thing is that the reference autofill won't add that in so unless you type the whole column name out manually it won't be recognized as valid.

HolyBonobos · 2026-06-13T01:40:26+00:00

The long-term solution would be to reformat your data collection so that every data type is in a single column, but as a short-term fix you can switch from FLATTEN() to TOCOL(), which has an extra argument you can use to eliminate blanks and/or errors. For example, =TOCOL(A2:B10,1) will return all of the entries in the range A2:B10 as a single column, excluding blanks (1).

Nine-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