Sunday, February 11, 2024

Excel validating whether required columns (marked with *) are filled or not

 Here is the formula

=LET(dataOriginal,A1:C100,

data,FILTER(dataOriginal,BYROW(dataOriginal,LAMBDA(row,SUM(--(row<>""))>0))),

firstRow,MAP(TAKE(data,1),LAMBDA(x,ISNUMBER(SEARCH("~*",x)))),

BYROW(MAP(DROP(data,1),LAMBDA(x,NOT(ISBLANK(x)))),LAMBDA(array, IF(SUMPRODUCT(--(array)*--(firstRow))=SUMPRODUCT(--(firstRow)),"OK","REQUIRED COL VALUE MISSING")))

)



No comments: