I looked at it some more and the only constant is the A$1 reference that
needs to be changed at the start of each group, then you just copy that down
as far as the end of the whole group you just pasted. Let me think some more
about making that a variable self-adjusting value. I was close a few minutes
ago while taking the second look, but not close enough.

"pfrank" wrote:

While I think you are heading in the right direction, I would still need to
change the ROW A$1 reference and copy down in my new group. However, my
actual worksheet is quite involved (about 300 rows) and I cannot copy down
the entire column since there are many rows of formulae that I don't want
written over. But, like I said, I think you are on the right track here, and
I am experimenting with OFFSET, ROW and COLUMN. Thanks!



"JLatham" wrote:

Try this for the formula in C2:
=B2+OFFSET(B2,ROW(A$1)-ROW(B2),COLUMN(A1)-COLUMN(B2))

extend that down through the first group. Now when you copy the entire
group and move it to another location, just change the ROW(A$1) at the top
C-column cell in the new group as required and again just drag the formula
down to the bottom of the new group. That was quick and dirty solution I
came up with real fast. May even be a better solution, and if so, someone
else will probably post it shortly.

"pfrank" wrote:

I have a several rows of formulae that refer to a constant at the upper left
corner of the range of formulae. I use an absolute cell reference to refer
to the constant, and many relative relative references in rows of formulae.
I want to copy this range of work and paste it several rows below the
original. However, I want to use a different constant for the second range
of formulae. Since I used an absolute reference in my original work, the
pasted formulae still refer to my original constant. How can I make the
reference to the original constant be absolute, yet relative to each range of
formulae so I can change the constant each time I paste the range of
formulae? For example:

A1=7
B2=100, C2=B2+A$1
B3=105, C3=B3+A$1
B4=110, C4=B4+A$1

Now, copy this range A1:C4 and paste down at A15, now we have:

A15=7
B16=100, C16=B16+A$1
B17=105, C17=B17+A$1
B18=110, C18=B18+A$1

However, what I really wanted was for A$15 to be my new constant (so I could
change the number in that cell) for this new range of formulae. My actual
formulae are much more numerous and complicated that this and I am trying not
to have to go in and manually change each cell reference for the constant on
my new range. It seems like I need some kind of formula in my original
constant cell reference (A$1) that will allow it to change when it is pasted
elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
make them work. Many thanks in advance for your thoughts.