Variable in formula

[Guest]

I have the following formula
=AVERAGE(OFFSET(BM51,-34,0,-12,1))

Next month, I will copy it one cell to the right and adjust the -34 to -35.
Is it possible through VBA to set the "-34" as a variable when it copies the
formula, and enter the new formula with something like "MyVariable -1". In
other words have it look to see what the current value is now, copy it to the
next cell and adjust the rows accordingly.

 

[Bob Phillips]

=AVERAGE(OFFSET(BM51,-33-COLUMN(A1),0,-12,1))

when you copy it next month the A1 will update to B1, so ,-33-COLUMN(A1),
will become ,-33-COLUMN(B1), which then evaluates to -35

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Perfect.

Thanks.

=AVERAGE(OFFSET(BM51,-33-COLUMN(A1),0,-12,1))

when you copy it next month the A1 will update to B1, so ,-33-COLUMN(A1),
will become ,-33-COLUMN(B1), which then evaluates to -35

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)