Drag and drop formula while skipping cells in the reference.

[Carlos90]

I was wondering if anyone could help me figure out this problem. In one sheet I have a list of totals that are every 6 rows. I would like to make a rolling total on the next sheet. For example, Sheet2!A1=Sheet1!A1, Sheet2!A2=Sheet1!A8+Sheet2!A1 and so on. Some one please help me with this problem.

[martindwilson]

sheet 2
a1=Sheet1!A1,
a2=SUM(INDEX(Sheet1!A:A,ROW()*7-6))+A1
fill a2 down

[Carlos90]

Thank you, that is what I need it to do. It worked when I did that exact thing in sheets that I added onto the workbook however, my data is located in between cells G20:G2738. When I put that into where you had A:A, I received a #VALUE! error. Any ideas?

Also, I noticed a difference between the formula I was using, I didn't use a comma in between the 7 and the -6 on the example you gave and it worked. However, when using a comma it returns the #VALUE! error as I mentioned. I fixed that, but now it keeps returning only the previous number in the total so I get a column of 10s.

I guess it may be reading the sum() function as a 0 and then adding the previous cell as it is designed.

[martindwilson]

a1=
=Sheet1!G20
a2=
=INDEX(Sheet1!$G$20:$G$2378,ROWS($A$1:A2)*7-6)+A1

[Carlos90]

Thank you for the example. I liked the idea of not having the number show up unless there was data for the week. However, when I did that, the number repeated as when it was simplified before until about 40-50 cells later when the "" was recognized. Not sure if you have any ideas as to why that occurs.

I tried seeing if I could do it another method and I tried =IF(C1=SUM(Sheet1!$G$20:$G$2378),"",INDEX(Sheet1!$G$20:$G$2378,ROWS($A$1:A2)*7-6)+C1, this worked better but it left cells blank if for a week the person didn't work any overtime and then the next week worked some hours. That will not work. That is what I have tried so far.

[martindwilson]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic