stupid quest - How to make excel NOT change formula when cop

[keith]

copy / paste and excel automatically tries to change the formula for me. I need to turn this OFF for a copy and paste that I am doing in a macro... if I could turn this feature off with some vb then turn it back on after the sub that would be perfect. But i'll take it any way I can get it..

 

[Steve Hartman]

What is excel changing in your formula when you copy/paste?

 

[keith]

changes some cell references sequentially based on where it is pasted. I only need 2 of the references changed though the other 15 references or so need to stay static.. here is fomrula:
=COUNTIF(A1:A5,A5)&" of "&CHOOSE(A5,itemkey!G3,itemkey!G4,itemkey!G5,itemkey!G6,itemkey!G7,itemkey!G8,itemkey!G9,itemkey!G10,itemkey!G11,itemkey!G12,itemkey!G13,itemkey!G14,itemkey!G15,itemkey!G16,itemkey!G17)
The "A5" reference is all i neeed to change sequentially.. all the rest stay static. I can have the macro change those for 3 ref's for me after I paste.. but didn't want the macor to have to change them ALL backwards .. geez does this make any sense?

 

[jeffmfrank]

change the range reference A1:A5 to $A$1:A$5

 

[keith]

I need to do this for all the "A5" references.. so I just add the $ anywhere applicable?

 

[Barrie Davidson]

On 2002-03-06 13:30, keith wrote:
I need to do this for all the "A5" references.. so I just add the $ anywhere applicable?

Yes, change A5 to read $A$5. Note that your reference to A1 will change unless you absolute reference it as well.

Regards,

 

[keith]

Sweet thats what I was looking for exactly! Man this forum is awsome! Here is the formula I ended up with for anyone who reads this message from archive:

=COUNTIF($A$1:A5,A5)&" of "&CHOOSE(A5,itemkey!$G$3,itemkey!$G$4,itemkey!$G$5,itemkey!$G$6,itemkey!$G$7,itemkey!$G$8,itemkey!$G$9,itemkey!$G$10,itemkey!$G$11,itemkey!$G$12,itemkey!$G$13,itemkey!$G$14,itemkey!$G$15,itemkey!$G$16,itemkey!$G$17)