Aale,

....perhaps

=column()-column($D$4)

so that the cell reference doesn't increment when copied.

Or, you could use =Column(A1)-1

and have the cell reference increment.

HTH,
Bernie
MS Excel MVP


"Aale de Winkel" wrote in message
...
The last statement works fine and trust me after decades of working with
magic squares (see: www.magichypercubes.com/Encyclopedia) I know your
repsonse here. what I looked for is replacening D4 with something like
"column()-column(D4)" in D4:M4 (or rather some column given the
'main-diagonal direction") this value thus serving as selector which broken
diagonal is summed over.
This doesn't seem to work, it looks like the statement is a single cel
target formula.
I therefore reckon copying the statement (along the target row/column) and
placing an index array somewhere is the way to go here, the statement is
already saving a lot of correcting during the copy paste sequence.
I currently haven't the time, but I'll experiment during the weekend with it.
(as said this kind of statement is new to me, I merely use Excell for idea
forming, but my friends use it more frequently)

Aale.

"Bernie Deitrick" wrote:

Aale,

It works for me - with D4 equal to 3, I get the sum of the cells K5, L6, M7, D8, E9, F10, G11,
H12,
I13, J14

Note that if you have a well behaved square of values, changing the index number will not
necessarily change the sum, so try entering slightly more randomized or staggered values.

HTH,
Bernie
MS Excel MVP


"Aale de Winkel" wrote in message
...
=SUM((D5:M14*((ROW(D5:M14)-1-(D4-10))=(COLUMN(D5:M14)))))+SUM((D5:M14*((ROW(D5:M14)-1-D4)=(COLUMN(D5:M14)))))

just wanted to add the above gave me the broken diagonal sum entered in cel
D4, changing D4 into the index of the target row / column doesn't seem to
work though.