Dragging SUM formula

What cell is your formula in, you could probably utilise indirect and row functions to get what you want. For example, if in A1 you have:
=sum('shift Data'!D3:D16)
and in A2 you want:
=sum('shift Data'!D17:D30)
then change A1 to :
=sum(indirect("'Shift Data'!D"& (ROW(A1)-1)*14+3 & ":D" & (ROW(A1)-1)*14+16))

Originally Posted by yudlugar

What cell is your formula in, you could probably utilise indirect and row functions to get what you want. For example, if in A1 you have:
=sum('shift Data'!D3:D16)
and in A2 you want:
=sum('shift Data'!D17:D30)
then change A1 to :
=sum(indirect("'Shift Data'!D"& (ROW(A1)-1)*14+3 & ":D" & (ROW(A1)-1)*14+16))

Thank you, this seems to work perfectly. Though I confess, I'm not sure why.

I have no experience of the 'indirect' function so I may be missing something. The cell that I am beginning with is B3. Leaving the A1 in your formula as it is makes the formula work fine, changing it to B3 makes it reference different cells (and returns the wrong answer), changing it to B1 makes it work fine. However A1 and B1 both have writing in or are blank. There must be something about it that I don't understand.

However, I have several columns and have copied it across, changing the column reference in the shift data sheet for each and it has worked for them all, so thank you.