Add certain rows....

[Zadig Galbaras]

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
-

[Arvi Laanemets]

Hi


D1=OFFSET($A$1,(ROW()-1)*25)


Arvi Laanemets


"Zadig Galbaras" <[email protected]> wrote in message
news:[email protected]...
> 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
> -
>
>

[Biff]

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
>-
>
>
>.
>

[Zadig Galbaras]

Thank you guys!

Now for a transaltion of function names from english to norwegian.....
There was a recommended spreadsheet I've got a year ago, but I seem to have
lost it.

Is there anyone who knows of such a help?
To translate english function names into norwegian?

--

regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-


"Zadig Galbaras" <[email protected]> skrev i melding
news:[email protected]...
> 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
> -
>
>

[Zadig Galbaras]

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" <[email protected]> skrev i melding
news:[email protected]...
> 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
>>-
>>
>>
>>.
>>

[Biff]

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" <[email protected]> skrev i melding
>news:[email protected]...
>> 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
>>>-
>>>
>>>
>>>.
>>>
>
>
>.
>

[Biff]

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" <[email protected]> skrev i melding
>>news:[email protected]...
>>> 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
>>>>-
>>>>
>>>>
>>>>.
>>>>
>>
>>
>>.
>>
>.
>