CODEWITHSUNDEEP
if-statementcheckboxgoogle-sheetsnestedgoogle-sheets-formula
Vladlen445
Vladlen445

Reputation: 37

Call a function if a checkbox is selected google sheets

I am trying to find a solution to the issue I have. I have checkboxes in column A. What I need is when one of them is checked, to populate a function/formula in a cell next to it (column B).

For example, I checked the checkbox in cell A10 and formula is being populated in cell B10.

The formula/function I have is a long IF function.

Hoping someone will be able to assist.

Upvotes: 0

Views: 192

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(TRANSPOSE(SPLIT(QUERY(QUERY({""; IFERROR(REPT("♦ ", LEN(SUBSTITUTE(TRANSPOSE(SPLIT(QUERY(IF(INDIRECT("A12:A"&
 MAX(IF(A12:A=TRUE, ROW(A12:A), )))=FALSE, "♣", "♥"),,999^99), "♥")), " ", ))-COUNTA(
 IF($B$11=Sheet5!A1,  FILTER(Sheet5!C1:C,   Sheet5!C1:C  <>"", NOT(REGEXMATCH(Sheet5!B1:B,   "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!J1,  FILTER(Sheet5!L1:L,   Sheet5!L1:L  <>"", NOT(REGEXMATCH(Sheet5!K1:K,   "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!S1,  FILTER(Sheet5!U1:U,   Sheet5!U1:U  <>"", NOT(REGEXMATCH(Sheet5!T1:T,   "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!AB1, FILTER(Sheet5!AD1:AD, Sheet5!AD1:AD<>"", NOT(REGEXMATCH(Sheet5!AC1:AC, "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!AK1, FILTER(Sheet5!AM1:AM, Sheet5!AM1:AM<>"", NOT(REGEXMATCH(Sheet5!AL1:AL, "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!AT1, FILTER(Sheet5!AV1:AV, Sheet5!AV1:AV<>"", NOT(REGEXMATCH(Sheet5!AU1:AU, "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!BC1, FILTER(Sheet5!BE1:BE, Sheet5!BE1:BE<>"", NOT(REGEXMATCH(Sheet5!BD1:BD, "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!BL1, FILTER(Sheet5!BN1:BN, Sheet5!BN1:BN<>"", NOT(REGEXMATCH(Sheet5!BM1:BM, "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!BU1, FILTER(Sheet5!BW1:BW, Sheet5!BW1:BW<>"", NOT(REGEXMATCH(Sheet5!BV1:BV, "MATERIAL|DISPOSAL|PLANTS"))),
 IF($B$11=Sheet5!CD1, FILTER(Sheet5!CF1:CF, Sheet5!CF1:CF<>"", NOT(REGEXMATCH(Sheet5!CE1:CE, "MATERIAL|DISPOSAL|PLANTS"))))
 ))))))))))+1))}, IF(COUNTIF(A12:A, TRUE)<2, "offset 1", ), 0)&QUERY(IF(A12:A=TRUE,
 IF($B$11=Sheet5!A1,  "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!C1:C,   NOT(REGEXMATCH(Sheet5!B1:B,   "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!J1,  "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!L1:L,   NOT(REGEXMATCH(Sheet5!K1:K,   "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!S1,  "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!U1:U,   NOT(REGEXMATCH(Sheet5!T1:T,   "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!AB1, "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!AD1:AD, NOT(REGEXMATCH(Sheet5!AC1:AC, "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!AK1, "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!AM1:AM, NOT(REGEXMATCH(Sheet5!AL1:AL, "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!AT1, "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!AV1:AV, NOT(REGEXMATCH(Sheet5!AU1:AU, "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!BC1, "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!BE1:BE, NOT(REGEXMATCH(Sheet5!BD1:BD, "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!BL1, "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!BN1:BN, NOT(REGEXMATCH(Sheet5!BM1:BM, "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!BU1, "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!BW1:BW, NOT(REGEXMATCH(Sheet5!BV1:BV, "MATERIAL|DISPOSAL|PLANTS")))),
 IF($B$11=Sheet5!CD1, "♦"&TEXTJOIN("♦", 1, FILTER(Sheet5!CF1:CF, NOT(REGEXMATCH(Sheet5!CE1:CE, "MATERIAL|DISPOSAL|PLANTS")))), 
 )))))))))), ), "where Col1 is not null", 0),,999^99), "♦")))

0

Upvotes: 2

Related Questions