At 14:02 11/09/2010 +0200, Johnny Rosenberg wrote:
I have a spreadsheet with 8 columns and I constantly add more rows.
One of the columns is a value of something, in this case time, but
that doesn't matter, it could be just any type of number. I usually
use auto filter on that sheet, but this time I needed one of two
conditions to be true for the same column (D), so a standard filter
seemed to be the way to go. So I opened the Standard filter dialogue
and made my filtered data to land at an empty sheet, starting at A1.
Then, on that new sheet, I summed one column, F. I got my value and
was happy with that.
However, remembered that I could do the same thing without needing
to copy the filtered data anywhere, by just filter on the same sheet
and then sum the visible cells by =SUBTOTAL(9;F1:F65536), but this
time I didn't get the same result! After maybe half an hour of
debugging I found that one of the rows got the wrong value in the
copy that I pasted on that empty sheet, since some of the rows in
column F are formulas. So one of the rows was like =F25-F23. That is
okay on the original sheet, but the filtered copy lacks one of the
rows and the formula gives therefore the wrong result.
I then opened the Standard filter dialogue again to see if there is
a way to paste the filtered data to a sheet with no formulas
involved, you know, like Paste Special with Formulas unchecked, but
I didn't find an option like that.
I think this would be a nice feature to add, or am I totally out of
my mind? Just wanted to hear your opinions before I report a feature request.
No, you are not out of your mind, but there are other ways of looking at this.
The problem does not occur just because you chose a different sheet
as your destination for the filtered values: the same effect can be
seen if you choose a destination on the same sheet. Any relative
addresses in formulas that are copied to other cells by the Standard
Filter are modified in the normal fashion, so your =F25-F23 would
change. There are two solutions to this, of course:
1. Change the addresses to absolute ones, =$F$25-$F$23 (or with a
subset of those $ signs as appropriate) - but perhaps there are
situations in which this would not be convenient.
2. First copy the data column to another (hiding it later if you
wish), using Paste Special (as you mention) to freeze the contents as
values instead of formulae, and then filter on that column.
In the case of pasting into another sheet, the absolute addressing
needs to be reinforced, and your example formula would need to become
something like =$Sheet1.$F$25+$Sheet1.$F$23 . This doesn't produce
quite what you ask for, since the filtered-and-copied values are
still dependent on the original arguments of your expression instead
of being values. But it may be useful.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
