Forum Discussion
How best to base conditional formatting on an array formula?
I have applied the conditional format by outputting the array to a helper range and then using single cell relative references as the format formula. Could I achieve the same thing without a helper range? What is your favoured approach?
Notes: The problem is based on material presented by Fellienne Hermans to demonstrate that Excel solutions are code. https://youtu.be/0yKf8TrLUOw
It is curious that the problem is easier to solve using relative referencing or CSE array breakup than modern dynamic arrays but the solution is of some interest in that it is the only time I have used nested SCAN helper functions.
2 Replies
Thanks for posting this, Peter! Inspired by it, I went deeper into LAMBDA functions and actually succeeded in creating one that I'm now using in my most active personal spreadsheet. I now see why LAMBDA is so highly touted.
Thanks for letting me know, John. I think the new venture should prove rewarding.
As for this particular problem of the maze, it is not the problem I would have picked to demonstrate the benefits of the approach; I picked it because the two internal references to elements of the same array makes the problem inherently difficult for array methods. That said, once I had the solution process as a function, Solveλ(grid), I was able to rotate the grid through 180 and re-solve effortlessly, so allowing me to test each cell to determine whether it is on a critical path or not.
