Search code examples
excelexcel-formulaexcel-365

Checkbox Grid into Single List Array

I'm new to using checkboxes to manipulate my excel files. I've got a grid of 51 check boxes, over 6 columns (as shown in the image linked below) is it possible to index the grid and give me a single list array showing which checkbox is showing as True ?

Example Page

Solution

  • Try something along the lines, assuming there is no Excel Constraints as per the tags posted, then this should work :

    enter image description here

    • Formula used in cell I2

    =LET(
     a, TOCOL(B2:G18),
     b, WRAPROWS(a,2),
     TOCOL(IFS(TAKE(b,,1),DROP(b,,1)),3))

    Or,

    =LET(
     α, WRAPROWS(TOCOL(B2:G18),2),
     TOCOL(IFS(TAKE(α,,1),DROP(α,,1)),3))

    If you need a sorted array then use the following formula:

    enter image description here

    • Formula used in cell I2

    =LET(
     α, WRAPROWS(TOCOL(B2:G18),2),
     σ,TOCOL(IFS(TAKE(α,,1),DROP(α,,1)),3),
     SORTBY(σ,TEXTAFTER(σ,CHAR(SEQUENCE(26,,65)))/1))