Auto fill cell addresses by not 1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

peter.c.bradley@gmail.com - 12 Dec 2007 17:22 GMT

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

Reply to this Message

Dave Peterson - 12 Dec 2007 18:05 GMT

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.

> Hi folks,
>
[quoted text clipped - 15 lines]
> Cheers,
> Peter

Signature

Dave Peterson

Reply to this Message

RagDyer - 12 Dec 2007 19:35 GMT

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))

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> How about a little work?
>
[quoted text clipped - 43 lines]
>> Cheers,
>> Peter

Reply to this Message