Andreas Saeger wrote:
Allen wrote:
Hi gang,
Is there any way to make a formula conditional depending on whether
the cells in the row referred to are visible or hidden.
What I need to do is create a summary of the sub-sections of a
standard so executives can see the overall but then also have the
detailed view available but not have the summary figures shown.
It would look approximately like this in summary mode:
ISO 27002-10 (Title) 5 8 1 3 6
each column would refer to an areas examined in a security assessment.
Then the expanded version would look like this approx:
ISO 27002-10 (Title)
ISO 27002-10.1 1 0 0 0 1
ISO 27002-10.2 0 2 1 0 0
ISO 27002-10.3 2 0 0 1 0
ISO 27002-10.4 0 0 0 1 3
ISO 27002-10.5 1 5 0 0 1
ISO 27002-10.6 1 1 0 1 1
So those who want a more granular view can get it by just unhiding the
rows but the totals at the chapter level would go away to prevent
reading the data as though there were more instances than reality
because people tend to sum what they see in a glance rather than
realizing that one is a summary line only.
If this is not possible - the client *really* wants this layout - I
would guess the best way is to do two sheets with one the summary view
that references the correct cells in the second, more granular sheet,
right?
Thanks,
Allen
Hi,
Function SUBTOTAL (as it is used by the built-in Data>Subtotals-wizard)
ignores filtered cells and other cells having SUBTOTAL.
What you really want is a database, I think. I'm pretty sure that you
will hit the wall of this calculator very soon.
Thanks Andrea.
A database would be a bit overkill as there would be maximum
number of areas around 15. The count, total could be highish -
50-60, but then it would be divided among up to 15 sub chapters.
What I don't understand is what condition would one evoke that
would make subtotal display if the other rows were hidden but
would *not* display if the other rows were not hidden.
But you may be right that a database - something I don't normally
use for small, static data sets - is the way to go, but I'm
having a hard time conceiving how to go about it. It would mean
two reports, one without details and one with details, which I
could achieve with two sheets of a spreadsheet. Reuse is easy
with a spreadsheet, just clear the contents of the populated
cells on the second sheet and update, which would clear the data
from the summary sheet. That seems easier than doing the same for
a database, but then I'm not a database wizard like some for I
hardly ever create them, just use what someone else has designed.
Best,
Allen
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
