referring to a cell 7 rows off

[sybmathics]

Hi,

In my spreadsheet I have numbers in column A.
In column B I want only a selection of the numbers from column A (every
seventh number)
Is there a way to ceate formulas in column B like te following:
=A1
=A8
=A15
without having to write every formula.

Any help is greatly appreciated

Sybolt

[Bob Phillips]

B1: =INDIRECT("A"&(ROW(A1)-1)*7+1)

and copy down

--
HTH

Bob Phillips

"sybmathics" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> In my spreadsheet I have numbers in column A.
> In column B I want only a selection of the numbers from column A (every
> seventh number)
> Is there a way to ceate formulas in column B like te following:
> =A1
> =A8
> =A15
> without having to write every formula.
>
> Any help is greatly appreciated
>
> Sybolt
>
>

[Roger Govier]

Hi

try entering in B1
=INDIRECT("A"&(ROW()-1)*7+1)
and copy down

Regards

Roger Govier


sybmathics wrote:
> Hi,
>
> In my spreadsheet I have numbers in column A.
> In column B I want only a selection of the numbers from column A (every
> seventh number)
> Is there a way to ceate formulas in column B like te following:
> =A1
> =A8
> =A15
> without having to write every formula.
>
> Any help is greatly appreciated
>
> Sybolt
>
>

[sybmathics]

Hi,

You both came up with the same solution.
It works. GREAT.

Thanks

[Bob Umlas]

And here's another:
Enter
A1
A8
(no "=" sign)
Grab both, use the fill handle, drag as far down as you want. You will get
A15, A22,...
Now select it all, use Edit/Replace, Replace A with =A and you're done! No
indirect function, no calculations, just straight direct references!
Bob Umlas

"sybmathics" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> In my spreadsheet I have numbers in column A.
> In column B I want only a selection of the numbers from column A (every
> seventh number)
> Is there a way to ceate formulas in column B like te following:
> =A1
> =A8
> =A15
> without having to write every formula.
>
> Any help is greatly appreciated
>
> Sybolt
>

[Bruno Campanini]

"Bob Umlas" <[email protected]> wrote in message
news:[email protected]...
> And here's another:
> Enter
> A1
> A8
> (no "=" sign)
> Grab both, use the fill handle, drag as far down as you want. You will get
> A15, A22,...
> Now select it all, use Edit/Replace, Replace A with =A and you're done! No
> indirect function, no calculations, just straight direct references!
> Bob Umlas

Very nice Bob, very clever!
I'll record this procedure among the best ones ever read.

Bruno

[sybmathics]

Yeah!

Briljant!!!

[Roger Govier]

Hi Bob

Very neat, and, as you say, no calculations. Thanks
Regards

Roger Govier


Bob Umlas wrote:
> And here's another:
> Enter
> A1
> A8
> (no "=" sign)
> Grab both, use the fill handle, drag as far down as you want. You will get
> A15, A22,...
> Now select it all, use Edit/Replace, Replace A with =A and you're done! No
> indirect function, no calculations, just straight direct references!
> Bob Umlas
>
> "sybmathics" <[email protected]> wrote in message
> news:[email protected]...
>
>>Hi,
>>
>>In my spreadsheet I have numbers in column A.
>>In column B I want only a selection of the numbers from column A (every
>>seventh number)
>>Is there a way to ceate formulas in column B like te following:
>>=A1
>>=A8
>>=A15
>>without having to write every formula.
>>
>>Any help is greatly appreciated
>>
>>Sybolt
>>
>
>
>

[KL]

Hi,

Just in case volatility and flexibility (your range may not necessarily
start at A1) is a concern:

=INDEX(A:A,ROW($A$1)+(ROW()-ROW($A$1))*7)

where $A$1 is the first cell in the range

Regards,
KL


"sybmathics" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> In my spreadsheet I have numbers in column A.
> In column B I want only a selection of the numbers from column A (every
> seventh number)
> Is there a way to ceate formulas in column B like te following:
> =A1
> =A8
> =A15
> without having to write every formula.
>
> Any help is greatly appreciated
>
> Sybolt
>