Help! Keep formulas from updating refs when copied??

[Ed]

This is getting frustrating! I've got a series of summary tables on one
page. Each table is taking data from the exact same locations on other
sheets (that is, one table will use Sheet1!B3, and the next table
Sheet2!B3). But each time I try to copy the summary table down the page,
hoping to simply change Sheet1! to Sheet2!, the B3 reference will
automatically update the number of rows I've copied down, and B3 is now B7!
I tried Copy>>Paste Special (Formulas), but I keep getting the same update
frustration! Any help?

Ed

 

[HansM]

This is where you use the function key F4 or manually insert the absolute
mark $ in front and after the cell letter reference.

Ie. what used to be A1 becomes $A$1 and now won't change the 1 when you
copy.

Experiment with using a $ mark on one side or the other to see how that can
also be helpful sometimes.

 

[Ed]

Thanks for the reply, Hans. I understand the $ - I didn't want to have to
go back in and change every reference!! I looked up the F4 key in Help and
it simply defined it as "Absolute/Relative". But I don't understand how to
use it. Can you explain a bit more?

Ed

 

[Guillermo]

The problem is that sometimes you don't really want the $ sign, because you
actaully need the values to change while constructing the table, and then
the only thing you need is to make an exact copy of your table.
I asked that before, and I really wish there was a "Paste exactly the same"
option in paste special, just like what happens when you cut and paste, but
leaving a copy in its original place. I have excel 2000 and I don't think
they've put something like that in all the newer versions...

a trick that works is:
select your range
edit/replace...... replace all "=" with something uncommon like #####
then you copy and paste (the formulas are no longer formulas so they won't
change)
then edit/replace #### with "=" again


guillermo

 

[Ken Wright]

John Walkenbach has that very option in his PUP6 addin, as well as a hell of
a lot more:-

http://j-walk.com/ss/pup/pup6/index.htm

There is a trial version on that link as well.

 

[Ken Wright]

<snip>

 

[Ed]

That sounds like an excellent idea, Guillermo. Thanks for the tip!

Ed

 

[Bill Ridgeway]

One further point on the use of absolute references.

If the two worksheets (pages) are in the same spreadsheet (file) you can
move the source cell and the absolute reference will change accordingly. If
however they are in different spreadsheets this will not, of course occur.

The moral of the story is to keep such cross references in the same file and
you will reduce the possibility of errors occurring.

Regards.

Bill Ridgeway
Computer Solutions