SUBTOTAL "ignores hidden values" functions

[Greg Woolsey <gr...@gmail.com>]

These are not yet implemented, I presume because they need to evaluate
auto-filter conditions on cells, and hide rows as needed?

This came up in a conversation with Vaadin over the conditional formatting
work I'm doing.

Anyone have any knowledge of that area, and the existing partial support
for that function?

That function comes up in a lot of example workbooks, and gets used in
table formulas all the time.  Not being able to properly calculate it is
annoying.

It looks to me like it would be something similar to data validation and
conditional formatting, with the simplification that it doesn't involve any
formula evaluation, just static rule definitions and dynamic cell values.

The tricky part is keeping the row hidden state in sync, I suppose.

Would it work to evaluate the function variations just based on whether the
row is hidden or not, and assume if values are changing, someone who cares
is keeping the filter evaluations and row states in sync external to the
function?

Re: SUBTOTAL "ignores hidden values" functions

[Dominik Stadler <do...@gmx.at>]

Hi,

I didn't do much in that area, so cannot comment on the specifics, but I
think only handling the hidden/ignored states when the function is
evaluated should be fine. If I understand correctly we already do similar
things in other places, i.e. asking users to call evaluation as part of
their code in order to have up-to-date results for functions.

Dominik.

On Fri, Feb 10, 2017 at 9:16 AM, Greg Woolsey <gr...@gmail.com>
wrote:

> These are not yet implemented, I presume because they need to evaluate
> auto-filter conditions on cells, and hide rows as needed?
>
> This came up in a conversation with Vaadin over the conditional formatting
> work I'm doing.
>
> Anyone have any knowledge of that area, and the existing partial support
> for that function?
>
> That function comes up in a lot of example workbooks, and gets used in
> table formulas all the time.  Not being able to properly calculate it is
> annoying.
>
> It looks to me like it would be something similar to data validation and
> conditional formatting, with the simplification that it doesn't involve any
> formula evaluation, just static rule definitions and dynamic cell values.
>
> The tricky part is keeping the row hidden state in sync, I suppose.
>
> Would it work to evaluate the function variations just based on whether the
> row is hidden or not, and assume if values are changing, someone who cares
> is keeping the filter evaluations and row states in sync external to the
> function?
>