excelvbaexcel-formula

"End of List" Message at the first blank Merged row - table

I am trying to get the values from CW column to CA column (It is a printable table structure starting from CA4 to CA36) CA column has merged cells(4 cells) - From CA4, CA8,CA12,CA16...CA36

CA4= IF(BW="","",BW4) dragable till CA36

I am trying, but could not attain the required result - "End of List" at the very first blank cell.

Please Help

I tried this formula but not getting required result =IF(COUNTIF($CA$3:$CA7,"End of List")>0,"",IFERROR(INDEX($BW$4:$BW$1000,SMALL(IF($BW$4:$BW$1000<>"",ROW($BW$4:$BW$1000),""),ROWS($CA$4:$CA8))-ROW($CA$3)),"End of List"))

I am trying, but could not attain the required result - "End of List" at the very first blank cell.

enter image description here

Solution

  • Try this:

    =LET(lst,FILTER(BW:BW,BW:BW<>""),num,COUNTA(lst),indx,ROW()/4,
          IF(indx<=num,INDEX(lst,indx,1),IF(indx=num+1,"End of list","")))

    My test:

    enter image description here