Auto fill cell addresses by not 1

[peter.c.bradley]

Hi folks,

Is there a way to auto-increment the cell address in a formula by an
increment other than 1. For example by 15:

=Fubar(A1,A2)
=Fubar(A16,A17)
=Fubar(A31,A32)
:
:
:

I have rather complex formulas that point to data on another excel
sheet where the data is organize into blocks (it is generated by
another program) rather than in nice sequential columns. Having to
manual enter the cell addresses for 200 rows is a PITA!

Cheers,
Peter

 

[Dave Peterson]

How about a little work?

Say I want the formula in row 1 (say E1).

I'd put this formula in E1:
="$$$=Fubar(a" & (ROW()-1)*15+1 & ",a" &(ROW()-1)*15+2 &")"
and drag down 200 rows.

I'd end up with something that looks kind of like the formula I wanted:
$$$=Fubar(a1,a2)
$$$=Fubar(a16,a17)
$$$=Fubar(a31,a32)
$$$=Fubar(a46,a47)
$$$=Fubar(a61,a62)
....

Next, I'd select that range and do
Edit|Copy
Edit|paste special|values

Then finally, with that range still selected:
Edit|Replace
what: $$$=
with: =
replace all

And excel will see those things as formulas.

 

[RagDyer]

OR, just enter these reference functions, and copy down:

=SUM(INDEX(A:A,15*ROWS($1:1)-14),INDEX(A:A,15*ROWS($1:1)-13))