Row visible by filter

**Arien** · 06-25-2009, 07:43 AM

I would like to make a boolean on every row in a list that tells me if that row is hidden by a filter or not.
(Want to use that for several sumproduct formula's)

Can that be done a formula or would I need VBA?

Thanks for your help.
Arien

**zbor** · 06-25-2009, 08:47 AM

How you see it with boolean if it's hidden?

**Arien** · 06-25-2009, 09:07 AM

I do not see it. But I can calculate with it...

I tried something like

Please Login or Register  to view this content.

But that that is just not correct.

Thanks.
Arien

**shg** · 06-25-2009, 11:14 AM

Try this:

Please Login or Register  to view this content.

Example usage: =IF(IsHidden(), 1, 0)

**Arien** · 06-26-2009, 06:00 AM

Thanks shg.
Please allow me to practice with it as it seems to slow down my computer pretty much.
Also I need to press F9 after entering the formula. Then it only calculates 1 cell, while the other cells change to #value

regards,
Arien

**shg** · 06-26-2009, 12:05 PM

I don't understand the problem you're having. It should be entered as an ordinary formula in a cell. Post a workbook?

The Application.Volatile causes the function to calculate when anything changes. It might be changed to reduce the impact.