Forum Discussion

Josh_Waldner's avatar
Josh_Waldner
Brass Contributor
Dec 04, 2021

index

Hello, everyone

I would like to find a way to index rows and columns depending on the color of the cells. place quantity of shaded cells may vary in the grid space.

see the attached for more details

excel

7 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 05, 2021

    Josh_Waldner you can't do that but in the attached I created 2 alternatives using conditional formatting.  In 1 option you add the cell coordinates in your lists and the corresponding cells in the grid will get colored.  In the other option you type a letter like "b" for blue or "o" for orange into the grid which will cause that cell to get colored that color (and the letter is hidden) and then the list is created below based on that.  (The formulas I used are based on Excel 365)

    • Josh_Waldner's avatar
      Josh_Waldner
      Brass Contributor
      Dec 06, 2021

      mtarler i tried revising your formula on sheet A, but it does not seem to work

      =IFS(ROW()-4<=AI5,
LET(b,IF(B2:X10="b",COLUMN(B2:X10)-1,"")&IF(B2:X10="b",INDEX(A1:A10,ROW(B2:X10)),""),
r,ROWS(b),
c,COLUMNS(b),
s,SEQUENCE(r*c,1,0),
list,INDEX(b,INT(s/c)+1,MOD(s,c)+1),
SORT(FILTER(list,list>""))),
ROW()-4<=(AI5+AI6),
LET(o,IF(B2:X10="o",COLUMN(B2:X10)-1,"")&IF(B2:X10="o",INDEX(A1:A10,ROW(B2:X10)),""),
r,ROWS(o),
c,COLUMNS(o),
s,SEQUENCE(r*c,1,0),
list,INDEX(o,INT(s/c)+1,MOD(s,c)+1),
SORT(FILTER(list,list>""))))

      is it truly not possible? If not i will write some VBA that will work, but it will have to be very long.

      i would like to get those two lists in one consecutive list.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Dec 06, 2021

        Josh_Waldner 

        For the conditional formatting I'd add some non-printable character at the end and format based on it. Like

        =LET(
   rTitle, $A2:$A10,
   cTitle, $B$11:$X$11,
   data,   $B$2:$X$10,

   rws,  ROWS( data ),
   cls,  COLUMNS( data ),
   k,    SEQUENCE( rws * cls ),
   nRow, INT( (k-1)/cls )+1, nCol, MOD( k-1, cls )+1,

  blue,   UNICHAR( 160 ),
  orange, " ",

getBlue,
LET( str, "b",
       check, --(data = str),
       all,
          IF( check = 0, "",  INDEX( cTitle, SEQUENCE(, cls ) * check ) ) &
          IF( check = 0, "",  INDEX( rTitle, SEQUENCE( rws ) * check ) ),

       lst, INDEX( all, nRow, nCol),
       FILTER( lst, lst <> "" ) & blue
),

getOrange,
LET( str, "o",
       check, --(data = str),
       all,
          IF( check = 0, "",  INDEX( cTitle, SEQUENCE(, cls ) * check ) ) &
          IF( check = 0, "",  INDEX( rTitle, SEQUENCE( rws ) * check ) ),

       lst, INDEX( all, nRow, nCol),
       FILTER( lst, lst <> "" ) & orange
),

stack,
LET(
     nB, ROWS( getBlue ),
     nO, ROWS( getOrange ),
     k,  SEQUENCE( nB + nO),
     IF( k <= nB, INDEX( getBlue, k ), INDEX( getOrange, k - nB ) ) ),

SORT( stack )

)

Resources