Brian Barker wrote:
At 14:32 18/10/2008 -0400, Bill Drescher wrote:
My spreadsheet has blood pressure values in sets of 3. I average
each them and then use the average of each set, which works fine.
But, I now have 2 rows that have the original data and nothing else,
and distract from the presentation.
So, I need to either copy every 3rd row to a new sheet or
automagically make the other 2 rows disappear from the original sheet.
I could do it manually, but I get new data frequently and would
prefer not to do all the work by hand.
Is there a way to tell calc to copy just every 3rd row from one sheet
to another, or to hide 2 of 3 rows ?
I think this is one of those problems that cry out "You shouldn't
start from here"! It seems that you are entering your original data
triplets in three rows of the same column and then using just one of
those rows - the third? - to enter or calculate further values. Is
there any reason why you cannot enter the data in three columns of the
same row?
I get the data as a csv and have no ability to change that.
Presumably not. If you did that, you would simply not have the
problem you describe. It would be easy, in that case, to spruce up
the presentation of the results in one of many ways: by copying the
block without the three data columns to another sheet, by hiding the
three data columns, or by defining a print range which excludes them.
But there are ways to do what you describe. Here's one. To copy
every third row of column A in Sheet1 to column A in Sheet2, enter
=INDIRECT("Sheet1.A"&3*CELL("ROW"))
in cell A1 of Sheet2. Drag to fill this down column A. CELL("ROW")
returns the row number of the cell containing the formula, and
multiplying this by three finds the row number of the required source
row in Sheet1. Concatenating this with "Sheet1.A" produces a text
string naming the required cell in Sheet1. But we don't want that
text string (e.g. "Sheet1.A3" in Sheets2.A1) to appear in Sheet2;
instead we want this string to be interpreted as a reference - and
that's what the INDIRECT() function does for us.
For further columns, you could amend the above formula manually to
contain other column names - B, C, and so on - in each case filling
the formula down the column. Alternatively, you could try something like
=INDIRECT("Sheet1."&CHAR(CELL("COL")+64)&3*CELL("ROW"))
in a cell of Sheet 2. Drag to fill this down the column and then -
with the column range still selected - also across multiple columns.
In this case, CELL("COL") returns the number of the current column - 1
for A, 2 for B, and so on. Offsetting these values by 64 and passing
them to the CHAR function converts these back to actual columns names
- "A", "B", and so on.
You can copy from different rows or columns either by adding an offset
to the row number or changing that for the column number.
Alternatively, the CELL() function can be referred to a different cell
from the one the formula is in by providing that cell as a second
parameter (e.g. CELL("COL";B1)), so you could use an explicit
different cell to achieve a similar effect.
I do have a feeling that there ought to be a simpler way, though ...
I trust this helps.
Indeed it does. Solves my problem and teaches me a lot.
Thanks
--
Bill Drescher
william {at} TechServSys {dot} com
