Forum Discussion
#Spill functions: dynamic data, dynamic tables, but no dynamic formatting? Is this acceptable?
I have a complex worksheet than can fetch several hundred lines of data using 4 database queries.
The help the user summarize and work through it interactively, I am working on an interactive dashboard, prompting for information brought from the queries, and summarizing them using the FILTER() command.
My first test case brought back five rows, unformatted. So I formatted them, then changed the company name. This gave me twenty rows, five formatted.
How am I to do this? Today, with the default input, there are 200 lines of input. But there is no guarantee a user is going to stick to the defaults. If some VP wants an overall look at the next 12 months, they might fetch 2,500 lines.
Am I to format all the way down to H1048576 just in case? Who wants to see DD/MM/YYYY on row one and 44372 on every row below?
It seems very strange Microsoft would choose to design it this way. They know this data will be dynamic. They are practically bragging about dynamic tables in their support pages. Yet they seemed to overlook that dynamic meant a variable return when it comes to formatting the results.
The solution seems so obvious, I don't know why it wasn't in place when #Spill functions were introduced. Format the same row you place your #Spill function and Excel dynamically formats the cells in the #Spill table to match.
After all, Excel already has the logic to detect the area the #Spill table will flow into. Why else do we get a warning message when we type a value into a cell below a #Spill function? Excel already has the function to copy formats. We can do that manually. (Home -> Paste -> Formatting [Alt-H,V,R])
1 Reply
Yes, and I would also like to be able to apply conditional formatting to a dynamic range. My normal technique for propagating a format is to click format painter with the anchor cell selected and then press Ctrl/Shift/Arrow, but that only goes as far as the current range. Another option is to apply the formatting all the way to the bottom of the sheet (don't stop short because that will mess up the used range).
If this is of immediate importance to you, Charles Williams FastExcel has the issue covered, including a floating totals row.
