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? 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.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
