Is this a sutable issue for an array process

WhiteKnight · November 21, 2021, 6:53am

Attached is a sheet with my own (clumsy?) attempt to produce something that I now suspect is an ARRAY process.

I’m sure some of you true magicians can point me in the right direction. Thanks in advance.

Firstly, in real use, the sheet is sorted descending on B and the dates C are filtered to exclude everything later than the current week, so it’s imperative that the process will still correctly account for the inversion of all the formulae. The “Grouped” columns are obviously normally hidden.

At the end of each calendar month, the overall array is extended into the boundaries defined at the bottom of the sheet to accommodate the additional days. O1:R3 help define the new dimensions and the appropriate 8 row array is simply replicated into the newly inserted extension and all the cells are thereby incorporated into all the Conditional Formatting and Range Expressions.

Conditional formatting is a real dog’s dinner as it creates new entries for each cluster of 8 rows so I end up with a shotgun approach to the columns with 20+ overlapping entries in the CF register for EVERY column. I’m becoming quite proficient at editing the dross.

The proliferation of filters permit almost infinitely variable selection criteria and some of the columns are filtered source data for another sheet in the file and four charts.

My procedure is to enter the value in C1:C5 which I want to count in the respective columns identified in D1:D5 with the results shown in E1:E5.

I’m aware that internally, the columns are numbered so O5:S5 are pressed into action as “Helpers” to accumulate the results for their respective columns. The individual cells in the column are simply “switches” to identify the existence of the defined value in the appropriate “real” column.

I’m sure I can learn at least two new things today.

joshua4 · November 21, 2021, 7:57pm

Maybe we can break this down a bit. Is the fundamental question this: Is there a way to update conditional formatting ranges from cell addresses (or row, column indices) that are themselves stored as cell values?

For example, can I have 512 in cell F1 and have a way for that to mean that I want a certain conditional format to be applied to/modified for column C from, say, row 5 to row 512.

Of course, then F1 could be set according to some function, but is setting the conditional format range the core question?

WhiteKnight · November 22, 2021, 4:15am

No. I only mentioned the CF as a side issue to demonstrate some of the pitfalls of extending the ranges.
The core question is related to the count of values identified in C1:C5 which are captured by the “helper” cells O7:S∞ and relate to the SUBTOTAL() filtering at O5:S5. I wondered whether there was a nested expression that could be applied to the five “result” cells D1:D5 which would simply use the array of original data J7:N∞. The most important aspect is that it must reproduce the SUBTOTAL() functionality and hopefully adjust the parameters to reflect the monthly “growth” - although if it’s only five expressions and it can’t cope with labels I could fairly easily adjust it at monthly extending time.
In the real world my sheet has 16000 “helper” cells and I wondered if it could be possible to remove them from the processor load.
Loading’s not of real concern as it’s still quite quick but then again, I wouldn’t tow a trailer behind my Koenigsegg.

eeigor · November 22, 2021, 4:51am

No. It’s a nightmare (or ****). But you need to be able to cope with this.

WhiteKnight · November 22, 2021, 5:17am

Thanks for that link. I haven’t yet experimented with any format painting to see if it’s easier/quicker but I will try that later.
The biggest problem is that my periodic extending includes many columns of formulae so It’s not quite as simple an issue as just the CF.
I’ve identified which calculated values will never change so I’m in the process of identifying where I can just pastevalue the historical values to cut the processing time.