Calculate rows and columns based on a cell range reference

[Mixmatch]

I'm hoping someone can help. I'm trying to sum a range in A1 and then based on that range, A2 will sum the same columns, but one row down. Added to this I want to be able to link B1 to A1 but to sum starting 12 columns later (i.e. every 12 months). The reason I'm not just filling down A1 to the end of my range is that it's non contiguous and also because I want to prevent any errors and so I only ever have to change the contents of 1 cell (A1) and all the other cells will work off this.

I'm very interested in learning how to do this rather than asking someone to come up with the answer - it's just I'm not sure where to start, I tried offset and the columns & row functions, but I could not get them to do anything useful.

If someone could point me in the right direction, that would be great.

Many thanks

John

 

[Mixmatch]

Hi,

I thought I'd solved it with an "=sum(offset", but it's not summing based on cell contents, only using this as the start point!

Cheers

John

 

[Sektor]

Can you explain it "on fingers"?

 

[Mixmatch]

Hi Sektor,

I'll try my best... if say the formula in A1 is =sum(D1:G1), I'd like A2 to be the sum of D2:G2 - but based on a reference to the range in A1 rather than simply copying the formula down from A1 - i.e. if A1 changed to =sum(H1:K1), then I wouldn't have to touch A2 as it would automatically change to sum H2:K2.

Hopefully once I've established this, I'd like B1 to calculate 12 columns worth of data based on the cell range in A1 but to start 12 columns over so I can have 12 months of data in each column which can be rolling.

I'm doing this as 1 have 14+ years of data in columns by month (i.e. quite a lot) and I need to move the data on by 1 month every month and wanted to achieve this by simply changing the range in A1.

I hope this helps - and thanks again for your earlier reply.

Many thanks

John