# Copy one cell fomula to another

1. ## Copy one cell fomula to another

I would like to copy one cell formula to another but i would like the constant to remain intact. When i pull it over to another cell there is an incremental effect.

from:
=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D8,"")))/(LEN(B4&","&D8))

to
=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B5&","&D8,"")))/(LEN(B5&","&D8))

But i want it to be either

=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D8,"")))/(LEN(B4&","&D8))

or better still simple increment just value
=SUM(LEN(B9:B300)-LEN(SUBSTITUTE(B9:B300,B4&","&D9,"")))/(LEN(B4&","&D9))

thanks  Register To Reply

2. ## Re: Copy one cell fomula to another

Assuming you want D8 to remain D8 change D8 to D\$8

The \$ in front of the 8 means the 8 will not change
You can also place a \$ infront of the D do the colunm D does not change

examples
\$D8 - Column will not change
D\$8 - Row will not change
\$D\$8 - Row & Column will not change

Your formula will be something like this - depending on what rows & columns are not to be changed
=SUM(LEN(\$B\$9:\$B\$300)-LEN(SUBSTITUTE(\$B\$9:\$B\$300,B4&","&D9,"")))/(LEN(B4&","&D9))

See help file on
Switch between relative, absolute, and mixed references  Register To Reply

3. ## Re: Copy one cell fomula to another

thank you :D got it running . you guys have been of a great help.  Register To Reply