referring to a cell 7 rows off

S

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
 
R

Roger Govier

Hi

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

Regards

Roger Govier
 
B

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
 
B

Bruno Campanini

Bob Umlas said:
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
Click to expand...

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

Bruno
 
R

Roger Govier

Hi Bob

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

Roger Govier
 
K

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
 
Top