Repeating inconsistent formula Help!

Hi all, I have a table that has a formula I need to reset every 12 cells (to represent months in a year). What I am calculating is tax paid monthly for the year.
So in cell 1 I have =H1
Cell 2 I have =sum(H$1$:H2)
Cell 3 I have =sum(H$1$:H3)
Cell 4 I have =sum(H$1$:H4)
Cell 5 I have =sum(H$1$:H5)
Cell 6 I have =sum(H$1$:H6)
Cell 7 I have =sum(H$1$:H7)
Cell 8 I have =sum(H$1$:H8)
Cell 9 I have =sum(H$1$:H9)
Cell 10 I have =sum(H$1$:H10)
Cell 11 I have =sum(H$1$:H11)
Cell 12 I have =sum(H$1$:H12)

Then in cell 13 I need the formula to start over for the new year with the following:
Cell 13 =H13
Cell 14 =sum(H$13$:H14)
Etc...

The problem is that I need this to repeat (reseting every 12 cells) for several thousand rows! I can enter this manually as above but this would take forever!! Is there a way to autofill or copy this formula to do this??

Thanks so much!

This looks pretty close but when I drag it out to autofill, it only repeats every 11 cells

Hi,

It works ok for me - in fact you don't even need the =H1 first, you can start with this and drag it down:

Formula:

Please Login or Register  to view this content.

If it's not working for you, can you maybe attach a sample file (with enough data to show the problem, but with any confidential info removed)?

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

it works but skips the 12th month of the first year, could it be because I'm starting on H2 and not H1?

Not sure
Here's my file:
Repeating resetting autofill 12m.xls

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

I cant attach a sample for some reason but if you change all the values in column H to 10500 and drag each column down to start on H2, it skips the 12th month of the first year for some reason.

Really not sure what's going on with that. Here's both a file showing the formulae in use, with your 10500 figure, and a picture in case you can't access the file.
Repeating resetting autofill 12m showing formulae.xls
Repeating resetting autofill 12m showing formulae.png
I'm about to leave work (or my wife will kill me) but I'll try and have a look again at home tonight.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Thanks Aardigspook, go home, hate for you to get in trouble with the wife. When you have a chance, can you make it work starting on H2 instead of H1? this is where I run into issues as I am using a table which has headers on row 1.

Thanks so much for your help.

Also, my work has an older version of Internet Explorer which does not allow me to attach a sample.

The formula I initially provided was created to be used in the H2 cell, and was working for me. What exactly is happening the 12th month?

I was able to take a screenshot of the problem im having

http://i150.photobucket.com/albums/s...m243/crop1.jpg

Oh! I understand, I thought your data started on H1

=SUM(INDIRECT("H"&(ROUNDUP((ROW(H2)-1)/12,0)-1)*12+1&":"&CELL("address",H2)))

That should do it.

Oh wait I just noticed something about your post:

Which way is it divided?:



H1 Nothing
H2 January
H3 February
...
H13 December
H14 January
H15 February
...


OR:


H1 Nothing
H2 January
H3 February
...
H13 December
H14 Nothing
H15 January
...

Hi,

Assuming your data starts in H2 then continues without any other interruptions, this should work:

Formula:

Please Login or Register  to view this content.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Its divided as follows:

H1 Nothing
H2 January
H3 February
...
H13 December
H14 January
H15 February

The formula above now has all months but it is doubling January every year after the first year see below:
http://i150.photobucket.com/albums/s...3/crop%202.jpg

This revised formula worked perfectly. Thank you so so much for your help!!!

Glad we were able to help.

Regards,
Aardigspook