referring to a cell 7 rows off

  • Thread starter Thread starter sybmathics
  • Start date Start date
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
 
Hi

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

Regards

Roger Govier
 
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
 
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
 
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
 
Back
Top