Conditional Formatting for Spilled Arrays

Hi Harun,

It is a little difficult to make an example file without adding a bunch of fake data/reports.  I'll try a bit of a narrative and see if that helps.  Imagine you had a customer list and you are trying to show how much in sales you had.  Say you had 5 customers and 5 products and you wanted a presentation where there is a header row  like, "Name", "Product 1 Sales", "Product 2 Sales", etc. (a total of six columns), a body where each customer gets their own row "(Customer A", "Customer B", etc.) and a totals row for each product so like "Totals", "=sum(product 1 sales)", "=sum(product 2 sales)", etc.  Now imagine you want that report formatted in a particular way, but your data is still changing.  So maybe you end up with 20 customers or 12 products, but you given the dynamic nature of the data, that could change between when you create the report and when you use it.

Traditionally what would do is just hand format each of the rows/columns/cells in the report range - say A1 to E1 for headers, A2 to A6 for the customer names, B2 to E6 for the total product amounts by customer, and A7 to A6 for the total amounts by product.

A fairly early alternative was to use pivot tables, so the customers would generate automatically and you could add a column for each new product - the main problem being that you are stuck with whatever pivot table formatting is made available to you within the general constraints of a pivot table.

More recently you can use something like groupby (a dynamic array formula that spills into a range of indefinite size).  So as you add products, the number of columns grows and as you add customers, the number of rows grow.  The big problem is that Excel does not natively have a way for you to format the header rows, the total rows, or the data rows except using the manual traditional method.  An alternative might be to use conditional formatting where you keep going back into the "applies to" range and updating the ranges to match the current size of your report's spilled array.

What my file does is sort of fudge the dynamic nature of a pivot table's formatting with the dynamism of the groupby spilled array.  It can't perfectly replicate the pivot table's built in conditioning, but it can approximate it without a huge amount of overhead.  In effect, by creating a conditional formatting applies to range that you know is always going to be bigger than your dynamic array's spilled range, you ensure that your spilled range is always formatted according to your rules. For example, if you say a conditional formatting rule applies to cells A1:Z1000, you've created the possibility for your dynamic array to have about 1000 customers and 25 products and still get covered by your rule.  Anything less than that, you are fine - anything more than that, your conditional formatting stops working.  The problem is, if you have your applies to range be the entire sheet, you may very well have a million cells checking to see what conditional formatting should be applied to them - if you had a dozen rules that might apply, that means you might have twelve million conditional checks.  The only way to avoid either not having your conditional formatting apply to your entire dynamic range and to avoid having every cell on the sheet checked to see how it should be formatted is to find a goldilocks sort of zone - one where just the right number of cells are checked - not too many, not too few.

My system is designed to automate the calculation of the goldilock zone and make it simple for you to set the applies to range for each conditional formatting rule.  The system also adds the ability for you to use something known as "declarative programming" like statements to select what portion of a dynamic array you want the rule applied to.  Rather than having to figure out how to have a condition apply just to the last row of your dynamic array (within the goldilocks applies to range), you just write a formula like cf.in_last_row(named_dynamic_array).  In this way, it is dead simple for you to understand what the conditional formatting rule is meant to achieve.

It is not the sort of solution the typical excel user would employ - it is very "abstract" in the sense that it is meant to apply to any problem within the general category of problems of conditionally formatting dynamic arrays.  You need to be able to understand what that problem is in the first place to have any appreciation for why you might use the tool offered.  So the likely user base is someone that is designing automated reporting for people that are otherwise unable to use macro enabled workbooks (VBA), ExcelScripts (effectively javascript), or custom addons into excel.  In other words, it is an entirely formulaic solution that will work on any computer that has excel 365 installed absent the system's admin disabling excel's name manager or modern excel 365 functionality.