Help regarding SUM formula

[thespardian]

On Sheet1, I have a data range D9:AH29.

https://www.dropbox.com/s/j3dq7acp2uet463/Data.png?dl=0

On sheet B, I want the total of each column. Currently I am using the formula =SUM(Sheet1!D$9:D$29) in Cell A1, =SUM(Sheet1!E$9:E$29) in A2, =SUM(Sheet1!E$9:E$29) in A3 and so on. The problem with the formula is ---- I have to manually change the column reference. Is there any dynamic way so that I may drag down the formula and the column reference change automatically?
Any help would be highly appreciated.

 

[tyija1995]

Hey,

I'd make a helper column on sheet B using column B.

B1:
="Sheet1!"&ADDRESS(9,ROW()+3)&":"&ADDRESS(29,ROW()+3)

Drag down to B31 (B31 should say "Sheet1!$AH$9:$AH$29")

Now in A1 use:
=SUM(INDIRECT(B1)) and drag down to A31.

 

[jorismoerings]

Hi,

Assuming you'll start in A1 on sheet B with the result of column D in sheet 1, try something like this:
=SUM(OFFSET(Sheet1!$D$9,0,ROW()-1,21,1))

As your range runs from 9 till 29 there are 21 cells to summarize. If the ranges changes, change 21 to the correct number of cells to sum.

 

[Muhammad_Usman]

Yes you can use sum-offset function to get rid of this problem.
As below

upload

Change Formula according to your needs.

 

[thespardian]

Hey,

I'd make a helper column on sheet B using column B.

.

Thanks for your input. But I cann't add a helper column.

 

[tyija1995]

OK no problem, in that case use the solution given by @jorismoerings - it's more efficient and doesn't require a helper column

 

[thespardian]

Thanks a lot for your guidance, jorismoerings and Muhammad_Usman. Offset is really a suitable option and serving the purpsoe.

I am thankful to you too tyija1995 for your input