OFFSET or INDIRECT formula - not sure

[carolynoi]

I have a very large spreadsheet with 12 worksheets (one for each month) and one Year To Date worksheet. On the YTD worksheet I would like to add just the totals from the other 12 worksheets. The totals are all 8 rows apart starting in cell D10 going through D1066.

I would like to have my formula automatically advance by 8 rows. Example: D10 would be the constant in the first formula. D18 would be constant in the next, D26 in the next and so on.

Example:
First formula is =SUM(Jan!D10+Feb!D10+Mar!D10)
Next formula is =SUM(Jan!D18+Feb!D18+Mar!D18)
nets would be D26 and so on.

Is there a way to drag this formula yet have it advance by 8 rows instead of 1 row?

[BadlySpelledBuoy]

How many of these formulas are you going to have?
Are you just trying to avoid having to update 12 lots of row references in each formula? If so you could use something like =SUM(Jan:Dec!D10) which would require the need to update far less.

If that's no use to you, perhaps post a sample workbook so we can fully understand your data layout?
It can more than likely be done with INDIRECT, but it's not always an ideal option due to it's volatile nature.

BSB

[Tony Valko]

First formula is =SUM(Jan!D10+Feb!D10+Mar!D10)

What cell will that formula be entered in?

[carolynoi]

It will start in D3 with =SUM(Jan!D10+Feb!D10+Mar!D10)
Then D4 with =SUM(Jan!D18+Feb!D18+Mar!D18)
Then D5 with =SUM(Jan!D26+Feb!D26+Mar!D26)
Then D6 and so on...

[carolynoi]

Now I feel like a dummy. Of course this will work =SUM(Jan:Dec!D10)

I believe I was over thinking the whole situation. It has to be copied 136 times but this is much simpler that my long formula.

Thank you

[BadlySpelledBuoy]

There are ways of doing this without the need to alter the formulas at all. However they're not quite so straightforward.

In the attached, the yellow section is a range named "SheetNames". For this method to work this list must be an accurate list of the sheet names you're going to sum.

The named range doesn't have to be where it is. It could easily be tucked away on a hidden sheet. I've just left it there so you can see what's going on.

I've added a formula to the green cells that can be dragged down and will hopefully do that you need here.

I'm sure the formula can be tidied up a little, but it works.

BSB

[Tony Valko]

If you only had a few months to total...

=INDEX(Jan!D$10:D$1066,ROWS(D$3:D3)*8-8+1)+INDEX(Feb!D$10:D$1066,ROWS(D$3:D3)*8-8+1)+INDEX(Mar!D$10:D$1066,ROWS(D$3:D3)*8-8+1)

Copied down as needed

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )