relative offset to a named cell

[Frank Van Eygen]

In cell A1 I have a cell named "Start_1", in cell A10 a cell named "End_1"
In columns B, C and D are numbers
In cell E1 a SQRT-calculation involving cells B1 to D2:
=SQRT(POWER((B1-B2);2)+POWER((C1-C2);2)+POWER((D1-D2);2))
In cell E1 a SQRT-calculation involving cells B2 to D3:
=SQRT(POWER((B2-B3);2)+POWER((C2-C3);2)+POWER((D2-D3);2))
and so on, untill E10 (where cell A10 named "End_1" is)
I would like to autofill column E using the named cells as a starting point
with OFFSET or an other function.
Until now I tride in cell E1:
=SQRT(POWER(((OFFSET(Start_1;0;1))-(OFFSET(Start_1;1;1)));2)+POWER(((OFFSET(Start_1;0;2))-(OFFSET(Start_1;1;2)));2)+POWER(((OFFSET(Start_1;0;3))-(OFFSET(Start_1;1;3)));2))
In cell E2:
=SQRT(POWER(((OFFSET(Start_1;1;1))-(OFFSET(Start_1;2;1)));2)+POWER(((OFFSET(Start_1;1;2))-(OFFSET(Start_1;2;2)));2)+POWER(((OFFSET(Start_1;1;3))-(OFFSET(Start_1;2;3)));2))
My question is how can I use the autofill function in column E so that I
don't have to change the rows and cols arguments manually in each OFFSET
function in the formula?
Is there a way to use a row-reference relative to a named cell in an OFFSET
function?

 

[Luke M]

I'm not sure why you're using OFFSET, as XL will automatically adjust your
formulas as you desired with your starting equation. But, since you ask...

=SQRT(POWER(((OFFSET(Start_1,ROW(A1)-1,1))-(OFFSET(Start_1,ROW(A1),1))),2)+POWER(((OFFSET(Start_1,ROW(A1)-1,2))-(OFFSET(Start_1,ROW(A1),2))),2)+POWER(((OFFSET(Start_1,ROW(A1)-1,3))-(OFFSET(Start_1,ROW(A1),3))),2))

Copying this down should result in the desired output. But again, your first
formula does the same thing, and is faster in calculation speed.

 

[Shane Devenshire]

Hi,

Using offset will result in far slower calculation speeds. Your question
would make more sense if you wanted to use range names or cell addresses with
INDIRECT to determine the start and end point for the calculations. As it is
you are making a simple solution in to a complex one.

Maybe you should tell us not how you want to change the formula by why.
AutoFill works just fine with your formula in its original form.

 

[Frank Van Eygen]

Thank you Luke M for the help.
The "But, since you ask..." is especially appreciated.

The idea is to write a macro for this formula (among a lot more) but make it
also adjustable “on-sheet†once the code has run.
By using “…ROW(A1)-1 and ROW(A1)†as you suggested it is possible for me to
recalculate the sheet by simply changing the Start_1 and End_1 positions and
auto-filling and dragging a little.

The basic function of the workbook is to calculate smooth 3D movement of a
welding robot and make adjustments (using Excel) between two “calibratedâ€
robot positions (Start_1 and End_1)