copy cell with formula containing the dollar

Hi,

sorry for this (maybe) obvious question. I have a quite long formula that contain the dollar function and I use it for calculating certain values across rows (so that one specific row is locked). Now I need to use the same formula (copy and paste) for calculating the same coefficients on the base of other rows which are far down the same sheet. Obviously the dollar keep the same row which is up on the sheet. Is there a way to copy and paste the formula so to lock the new row (same columns but different row) and do the same thing?

It would save me a lot of time as I have dozens of matrix for which I have to apply the same formula

Thank you

Best

Ted

Could you provide us with 2 examples of the formula, 1 before and 1 after, so that we can see how you need it to change.

Knowing which cells each of those formula will go into would also be useful.

Sure, this is the formula, that needs to be applied to a set of rows (B5 until B9 with B5 locked)

=SQRT(($B$5-B5)^2+($C$5-C5)^2+($D$5-D5)^2+($E$5-E5)^2+($F$5-F5)^2+($G$5-G5)^2+($H$5-H5)^2+($I$5-I5)^2+($J$5-J5)^2+($K$5-K5)^2+($L$5-L5)^2+($M$5-M5)^2+($N$5-N5)^2)

I need to this for lots of other matrices (rows B10 to B16, with B10 locked, B17 to B23 with B17 locked and so on for many others). Hope it is clearer, also that it would same me lots of time if there is a way to copy and paste so that when I copy the formula I have on B5, it will lock B10)

The first block has 5 rows "locked" (B5:B9) but then it changes to 7 (B10:B16) ???

I am desperate Just one row is blocked (the range changes but not what it need to be done, that is to calculate a distance between one row and a set of rows)

Originally Posted by Carmine

=SQRT(($B$5-B5)^2+($C$5-C5)^2+($D$5-D5)^2+($E$5-E5)^2+($F$5-F5)^2+($G$5-G5)^2
+($H$5-H5)^2+($I$5-I5)^2+($J$5-J5)^2+($K$5-K5)^2+($L$5-L5)^2+($M$5-M5)^2+($N$5-N5)^2)

Like this ...

=SQRT(SUMPRODUCT(($B$5:$N$5-B5:N5)*($B$5:$N$5-B5:N5)))

Originally Posted by Phuocam

Like this ...

=SQRT(SUMPRODUCT(($B$5:$N$5-B5:N5)*($B$5:$N$5-B5:N5)))

Not entirely what was asked for, Phuocam, but you have found what I was missing.

Combining your formula, with the method I had for defining the correct range,

=SQRT(SUMPRODUCT((INDEX($B:$N,ROW()-ROWS(B$1:B1)+1,0)-INDEX(B:N,ROW(),0))*(INDEX($B:$N,ROW()-ROWS(B$1:B1)+1,0)-INDEX(B:N,ROW(),0))))

Carmine, please note that you need to copy the formula when you paste to a new range, not the cell (copying the cell will cause the references to lock / increase the same as a normal formula would).