Typo correction:
D6 OFFSET(A$1,50,0) Returns the value in cell A51
Should read:
D3 OFFSET(A$1,50,0) Returns the value in cell A51
Biff
-----Original Message-----
Hi!
Which formula did you try?
The formula offered in the other reply would have
returned
an error because a required argument was left out. If you
used my example:
=OFFSET(A$1,(ROW()-1)*25,,)
it should have worked if you did indeed get it translated
correctly and it was placed in cell D1, where you said
you
wanted it.
Here are the arguments to OFFSET
Reference_Cell,Rows,Columns,Height,Width
In your case the Reference cell is A1. When entered like
this: A$1 and copied down A$1 should not change to A$2 or
A$3 etc.. The row number should remain ABSOLUTE.
The Rows argument is how many rows offset from cell A$1.
In your case the Rows argument is: (ROW()-1)*25.
Here's what that means:
The ROW number that the formula is in minus 1 then
multiplied by 25. So, if you enter that formula in D1:
Row(1)-1 = 0 * 25 = 0
Thus:
=OFFSET(A$1,0
Now, since all the data you want is in the same column as
the reference cell you do not want to offset A$1 by any
columns but the column argument is required so you just
leave that blank or enter zero:
=OFFSET(A$1,(ROW()-1)*25,,)
Which is the same as:
=OFFSET(A$1,(ROW()-1)*25,0,)
Since you're only returning a single value the Height and
Width arguments are not needed and they are also OPTIONAL
arguments.
So, as you copy the formula down a column starting in
cell
D1:
D1 OFFSET(A$1,0,0) Returns the value in cell A1
D2 OFFSET(A$1,25,0) Returns the value in cell A26
D6 OFFSET(A$1,50,0) Returns the value in cell A51
etc..
etc..
Biff
-----Original Message-----
Hi..
I have translated it into Norwegian, and typed your
suggestions, but....
First of all I've got an error so I changed the formula
to this:
=OFFSET(A$1;2;;;)
According to Excel Help the first parameter is the cell
of origin. The
second is the offset size. The third, fourth and fifth I
can forget this
time.
Anyhow. After typing the formula and got the right value
shown in the first
cell, I copied downwards and the values came out wrong.
The A$1 changed to A$2, and the value shown was the
value
of the cell
situated two cells below A2. That was not what I wanted.
This led to a list
where every number in the column was shown, not every
second.
If I locked the entire first parameter with the cell of
origin, I got the
same value as in the in the first in all the cells below
the first.
I wanted to be able to pick out every second or 25.th,
for that matter,
value in a column of values, and show them in a column
of
adjacent cells.
--
regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-
"Biff" skrev i melding
...
Hi!
In D1 enter this formula and copy down as needed:
=OFFSET(A$1,(ROW()-1)*25,,)
Biff
-----Original Message-----
Hi.
My spreadsheet has a repetitive calculation for each
25.th row.
In another place I want to list up each of these
values,
but then in each
and every row downwards.
That is;
A1 = 25
A26 = 26
A51 = 23
Now I want to copy these values to lets say to D1 like
this
D1 = 25 - Linked to A1
D2 = 26 - Linked to A26
D3 = 23 - Linked to A51
Is there a trick in the formula in D2 to automatically
jump 25 rows from the
row mentioned in D1, so it shows the value in A26,
without me having to sit
here day and night in a week to do the tedious copy and
link jockey routine?
--
regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-
.
.
.
|