Excell Formulas

[Wolfsexor]

I am attempting to copy and paste and advanced, complex formulae from one cell to another, but when I do certain values in the cell change. How do I prevent particular values from changing when I copy/Paste?

Example:

sum=(c1*4)+('materials'B12*3)

When I copy and then paste the formula chances thusly:

sum=(c2*4)+('materials'B13*3)
or
sum=(d1*4)+('materials'C12*3)

How do I tell Excell that I do want part of the formula (c2*4) to change accordingly, but prevent ('materials'B12*3) from changing?

Currently I have to copy the entire formula to notepad, do a find/replace, and copy the formula back into the cell I want to use... with thousands of entries this is a PITA, to be sure.

Also, is there a way to do a global find/replace within all cells?

[osprey4]

Hi, Wolfs:

Welcome to TechIMO.

When you copy and paste, Excel assumes you want to use relative addressing. Sounds like you need to use absolute addressing. Add a dollar sign before each column or row number of an address and it will not change when you paste.

See this page for more help: Excel XP/X: Editing Cell Contents, UWEC

[butch81385]

To keep a number absolute between cells you would use the $ sign.

So, for your example, instead of B12 you would want $B$12 ($B makes it so that B will not change if you move the cell left to right, and $12 makes it so that 12 does not change when you copy the cell up and down).

[Wolfsexor]

Ahhhh, many thanks!
I was told that the dollar sign did something like not, but the person didn't exactly know how... *LOL*

[butch81385]

No problem!

You should stick around and explore TechIMO for a while... This is a great place to learn and help (and make some good friends in the process)!