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

 

[Roger Govier]

Hi

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

Regards

Roger Govier

 

[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

 

[Bruno Campanini]

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

 

[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