Forum Discussion
Conditional Formatting via Lambda
This has nothing to do with LAMBDAs vs non-LAMBDAs; rather, arrays vs ranges. Conditional Formatting requires that a given rule be defined relative to the range reference(s) of the cell(s) to which it is applied. Since arrays do not by definition meet this criterion, they cannot be used as the basis for Conditional Formatting.
The only way to circumvent this would be to INDEX the array, though this would necessitate an explicit reference to the range to which the Conditional Formatting is being applied. For example, with MyArray defined as:
={1,0,0;0,1,0;0,0,1}
you could then apply the following Conditional Formatting rule to the range D1:F3:
=INDEX(
MyArray,
ROW() - ROW(INDEX($D$1:$F$3, 1, 1)) + 1,
COLUMN() - COLUMN(INDEX($D$1:$F$3, 1, 1)) + 1
)
I understand, however, that this need to include an explicit cell reference is not what you were hoping for.
Regards
JosWoolleyThank you for your thoughts, your answer held the key. I don't need to realize the array into a range on a sheet. I can do something like this:
Fmt = LAMBDA({"blue"; "bold|blue"; "bold"}); CondFmt = LAMBDA(corner,row,col,fmttest, LET( rrow,row-ROW(corner)+1, rcol,col-COLUMN(corner)+1, formats,INDEX(Fmt(),rrow,rcol), formatarr,TEXTSPLIT(formats,"|"), r,NOT(ISNA(MATCH(fmttest,formatarr,0))), r ) );My conditional formulas are like this:
=CondFmt($B$29,ROW(B29),COLUMN(B29),"blue") => [make blue]
=CondFmt($B$29,ROW(B29),COLUMN(B29),"blue") => make bold
I can now create a report and control its formatting with lambdas only. The only missing piece is the ability to scale the conditional formatting to the extent of the report spill. Maybe one day!
