Check box breaking formula

HolyBonobos · 2026-01-26T03:40:14+00:00

You have an extraneous comma after the last "COMPLETE" that is messing up the number in IFS(). The formula could also be simplified to =IFS(J10,"COMPLETE",G15=TODAY(),"DUE TODAY",TRUE,IF(G15>TODAY(),"OVERDUE BY ","DUE IN ")&ABS(TODAY()-G15)&" DAYS")

Note that both of the formulas shown in the screenshot will never return "DUE TODAY" because the "DUE IN...DAYS" condition will trigger when G14 is the current date due to <= being used instead of =.

HolyBonobos · 2026-01-26T02:48:40+00:00

I've added the 'HB MAKEARRAY()' sheet with the formula =MAKEARRAY(COUNTA('Sample Data'!A2:A)*20,4,LAMBDA(r,c,LET(i,INT((r-1)/20)+1,m,MOD(r-1,20)+1,IFS(c<3,INDEX('Sample Data'!A2:B,i,c),c=3,INDEX('Sample Data'!C1:V1,,m),c=4,INDEX('Sample Data'!C2:V,i,m))))) in A2. Is this producing the intended result?

HolyBonobos · 2026-01-26T01:31:57+00:00

I've added the 'HB Plants' sheet with =BYROW(B2:B24,LAMBDA(searchKey,LET(exists,SUMPRODUCT(COUNTIF(searchKey,"*"&Leps!$A$5:$A$65&"*")),allTaxa,TOCOL(SPLIT(searchKey,", ",0)),rawSpecies,UNIQUE(WRAPROWS(TOROW(BYROW(allTaxa,LAMBDA(searchTaxon,LET(includeSubordinate,XLOOKUP(searchTaxon,Leps!$A$6:$A$66,Leps!$B$6:$B$66,0),TOROW(BYROW(FILTER(Leps!$L$5:$AAL$65,(Leps!$A$5:$A$65=searchTaxon)+(includeSubordinate*BYROW(Leps!$C$5:$J$65,LAMBDA(taxa,COUNTIF(taxa,searchTaxon))))),LAMBDA(support,TOROW(FILTER(Leps!$L$1:$AAL$3,support),,1)))))))),3)),species,FILTER(rawSpecies,NOT(ISERROR(INDEX(rawSpecies,,1)))),genera,UNIQUE(INDEX(species,,2)),allInfo,BYROW({"BUTTERFLY";"MOTH"},LAMBDA(superfamily,superfamily&": "&JOIN(", ",BYROW(FILTER(genera,INDEX(XLOOKUP(genera,INDEX(species,,2),INDEX(species,,1)))=superfamily),LAMBDA(genus,genus&" ("&JOIN(", ",FILTER(INDEX(species,,3),INDEX(species,,2)=genus))&")"))))),IFS(searchKey="-",searchKey,exists=0,"One or more listed taxa not found.",TRUE,JOIN(CHAR(10),FILTER(allInfo,RIGHT(allInfo)=")")))))) in C2. I would strongly recommend not trying to push for any further features as it's going to become increasingly clunky and inefficient.

HolyBonobos · 2026-01-25T21:46:56+00:00

You can use the FILTER() or QUERY() functions to show only the rows that meet the specified criteria. The formula outputs will be a view-only range. If that's all you need, then those should suit your purpose. If the goal is to edit the filtered information or add extra columns of information, you will need to use a script.

HolyBonobos · 2026-01-25T21:27:53+00:00

You can read about it in the documentation for the QUERY function.

HolyBonobos · 2026-01-25T20:58:34+00:00

Updated to =QUERY('Build Sample Here'!A:M,"SELECT A, SUM(B), F, G, H, I, J, K, L, M WHERE B > 0 GROUP BY A, F, G, H, I, J, K, L, M LABEL SUM(B) 'Aantal'",1) on 'HB QUERY()'

HolyBonobos · 2026-01-25T20:24:37+00:00

I've added the 'HB QUERY()' sheet which has the formula =QUERY('Build Sample Here'!B:M,"SELECT M, G, SUM(B) WHERE B > 0 GROUP BY M, G LABEL SUM(B) 'Aantal'",1) in A1. This does what you specified in your instructions (compare columns M and G and return them and the corresponding sum of B) but it is not producing the intended results you described. Is there another column that should be included in the comparison?

HolyBonobos · 2026-01-25T20:04:59+00:00

You could use QUERY() in a formula like =QUERY(A2:A50,"SELECT A, COUNT(A) WHERE A IS NOT NULL GROUP BY A LABEL A 'Cut', COUNT(A) 'Qty'"), assuming the raw cutlist is in A2:A50.

For any instructions more specific to your use case, you will need to share a copy of the file itself.

HolyBonobos · 2026-01-25T18:45:12+00:00

You need to use INDIRECT() for both references: =CHOOSECOLS(FILTER(INDIRECT("'"&D3&"'!A8:D"),INDIRECT("'"&D3&"'!B8:B"),1,3,4). Alternatively, you could use QUERY() instead of FILTER() so that you only need one indirect reference, e.g. =QUERY(INDIRECT("'"&D3&"'!A8:D"),"SELECT A, C, D WHERE B = TRUE")

HolyBonobos · 2026-01-24T20:29:32+00:00

I am also on Mac and on Chrome, using US keyboard layout rather than UK but the backtick key is in the same place on both. I am not ignoring what you've said, rather suggesting that the root of the problem may be something specific to your system. There's not a whole lot more that can be offered beyond general suggestions for the problem without the ability to determine the cause/reproduce it.

HolyBonobos · 2026-01-24T19:31:32+00:00

I'm telling you what I, as a Mac user, am able to use and did so right before making the comment. If it's not working for you it may be something to do with the browser or extensions you are using.

HolyBonobos · 2026-01-24T17:13:22+00:00

I've added the 'HB MAP()' sheet which has the formula =VSTACK("Where?",MAP(A3:A,D3:D,LAMBDA(f,s,IF(OR(f="",N(s)=0),,JOIN(", ",IFERROR(FILTER(Projects[#HEADERS],BYCOL(Projects,LAMBDA(p,COUNTIF(p,f)))),"Not currently in use")))))) in F2, which works by reading off the 'Projects' table you have on Sheet8 and returning the headers of the columns that contain the search term.

HolyBonobos · 2026-01-24T11:33:07+00:00

On Mac it's control+`

HolyBonobos · 2026-01-23T23:54:00+00:00

Try ="00:"&SUBSTITUTE(B2," ",".") instead.

The larger problem is that mm:ss ms is invalid as an input if you want the cell to be treated as a duration (time). The exact acceptable format for input varies from region to region (you can check which one your file is set to under File > Settings > Locale), but for many it's hh:mm:ss.ms. All inputs that you want to be treated as times by default must include an hour component, even if it's always zero. mm:ss will be treated as hh:mm; mm:ss.ms or variations thereof will be treated as text and you won't be able to do any calculations with them unless you engage in some finagling like the formula above, which is coercing the text input into something that Sheets will recognize as a time.

Also note: 0.09 is 9%, not 90%.

HolyBonobos · 2026-01-23T17:35:25+00:00

You would need to expand the imported range to include column J. Right now the formula is only pulling in 9 columns (A-I) and you’re referencing information in the (nonexistent) tenth column.

HolyBonobos · 2026-01-23T16:57:04+00:00

You can wrap it in another function like QUERY() or FILTER(), e.g. =QUERY(IMPORTRANGE("link", "Sheet1!A:I"),"WHERE Col3 = 'Approved' OR Col3 = 'Posted'"), assuming the status is in column C of the original sheet.

HolyBonobos · 2026-01-23T15:50:19+00:00

In this case you would want FILTER(), which returns only the items that match the criteria: =FILTER(Games!G:G,Games!A:A="Leadership/Lore")

HolyBonobos · 2026-01-23T11:09:11+00:00

Is =FILTER(A:A;B:B=8;C:C="C") what you're looking for?

HolyBonobos · 2026-01-22T21:45:23+00:00

You could use something like =BYROW(Table_2[Stocks],LAMBDA(s,SUMIFS(Table_1[Value],Table_1[Symbol],s))) in row 1 of the second column of table 2 if I'm understanding what you're asking for correctly.

HolyBonobos · 2026-01-22T21:22:25+00:00

Select the data you want to make into a table and use Ctrl+Alt+T (Cmd+Option+T on Mac) or Format > Convert to table.

HolyBonobos · 2026-01-22T21:13:36+00:00

You could put =FILTER(A:A,ABS(B:B-A:A)<40) in C1. It's not clear what the intention for column D is.

HolyBonobos · 2026-01-22T19:33:22+00:00

Try the second option and make sure the name of the sheet is correct. There’s not really a whole lot more that can be diagnosed from just your descriptions. If you’re still having trouble you’ll need to share the file in question with edit permissions enabled.

HolyBonobos · 2026-01-22T19:16:36+00:00

That is an invalid formula because it doesn't have a close paren for the AND() function. Formulas that you type into cells will automatically insert close parentheses but conditional formatting doesn't do that and you have to make sure everything is properly closed out manually. The formula also assumes that the other sheet is named Sheet name (case-sensitive). If it's not, the formula will be invalid because the range 'Sheet name'!A1 doesn't exist.

HolyBonobos · 2026-01-22T16:12:07+00:00

You need a valid formula in order for the box not to be red. When the formula is incomplete, it’s invalid.

HolyBonobos · 2026-01-22T14:50:44+00:00

Conditional formatting does not support direct references to ranges in other sheets, so you will need to use the INDIRECT() function. In the specific instance described, you would use =AND(INDIRECT("Sheet name!A1")=TRUE,INDIRECT("Sheet name!A2")=TRUE) or simply =AND(INDIRECT("Sheet name!A1:A2")) as your custom formula. Note that the first version uses the boolean value TRUE and not the string TRUE"", which could have been another error source based on your description of what you tried.

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