Increase/Drecrease Column by Number in CELL A2

[koskesh]

Hello,

I am using a workbook to track weekly sales vs the forecast
It starts by weeks 36 of this year and ends week 10.2015

AIM:
CELL A2: (value changes every week) For example 2

SUM(H12:L12) -> add 2 columns =SUM(H12:N12)

and another formula to decrease the columns
for example
SUM(L12:Z12) to SUM(N12:Z12) decrease the first part of the sums by 2 columns

Hope this clears it up
Thanks for the help

 

[Somendra Misra]

Hi,

Better you explain this with a sample file with some data.

Regards,

 

[koskesh]

Hi Somendra,

found a way to do it
=SUM(H11:INDEX(11:11;COLUMN(H11)-1+A1))

Regards

 

[prasaddn]

Hi Koskesh,

I think there is some typo or error in your formula in index part.

If you have not found any other alternate, here is what I figured out:

first requirement:
=SUM(INDIRECT(CONCATENATE(ADDRESS(ROW(H12),COLUMN(H12)),":",CHAR((78)+A2),ROW(H12))))

second requirement:
=SUM(INDIRECT(CONCATENATE(CHAR((76)+A2),ROW(H12),":",ADDRESS(ROW(Z12),COLUMN(Z12)),)))

Note:
Char(78) return L, while char(76) returns L. And it adds the number of columns specified in A2. One can directly give the "H12", but i used the address just to keep it more consistent.

Regards,
Prasad DN