The next best thing I can think of is to use Replace. Copy a group from one
location on the worksheet to the new location. Note where the constant value
(A1 = 7) was in the old group and where it now appears in the new group.

With the entire group still selected use Edit | Replace to change (A$1) to
the new location, as (A$12) and choose Replace All. Since that's the only
place in any of the cells where I see a $ symbol, then it should give you no
problems at all if everything else in the group of cells copied and pasted is
as you've described here.


"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.