1

hi and thank you for reading my post. please help me with my array formula. the goal of the formula is to display the status on the column of my work sheet (queue, working, done and blank). it works perfectly when all the cells are filled up prior to pasting but it displays an error "no match" whenever i edit a cell on the pre-filled sheet or whenever i add a row to the sheet. also i would like to change the error message whenever it returns the n/a result but failed in incorporating the iferror statement to it's logic. the formula is pasted below.

={"Status";
ArrayFormula(
    IFS(    
        ISBLANK($A$2:$A),"",
        $G$2:$G>=1,"queue";
        AND(ISTEXT($A$2:$A),NOT(ISBLANK($B$2:$B)),ISTEXT($C$2:$C),ISURL($D$2:$D),(ISTEXT($E$2:$E)),NOT(ISBLANK($F$2:$F)),$G$2:$G=0,$H$2:$H>=1,NOT(ISBLANK($I$2:$I))),"done";
        AND(ISTEXT($A$2:$A),NOT(ISBLANK($B$2:$B)),ISTEXT($C$2:$C),ISURL($D$2:$D),ISTEXT($E$2:$E)),"working"

        )
    )
}

the ifs statement work if i individually paste it on the column but my goal is to have the array formula pre populate the column whenever i add a row to it or update a data on the column.

also the formula above only displays the "" and the queue formula when i edit the sheet and if the criteria is not met for the two, it just displays n/a

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.