Excel Formula

[bambi4thomas]

I'm looking for a formula to calculate the sum of a cell in 16
workbooks, of every 12 rows.
example =sum(Sheet1!b2,Sheet2!b2,Sheet3!b2)... and so on)

Then the next line down I want to calculate =sum(Sheet1!b14,Sheet2!
b14,Sheet3!b14)...
If I copy the formula from above it just goes to b3, of course.
Is there a way to calculate on each line every 12 rows of the other 16
workbooks?

 

[Pete_UK]

Yes, you can do it with OFFSET and INDIRECT.

But, instead of having your long SUM formula, you can shorten this to:

=SUM(Sheet1:Sheet16!B2)

as long as the only sheets between Sheet1 and Sheet16 are the ones you
want to add from.

Hope this helps.

Pete

 

[bambi4thomas]

Thank you, I'll try those.
I've tried the =SUM(Sheet1:Sheet16!B2) but for some reason it won't
work. It just comes up as #VALUE and I can't figure out why?

 

[Dave Peterson]

Does any sheet have a #value error in B2?

ps. I like to insert a couple of sheets. I put one to the far left (named
Start) and one to the far right (Named End).

Then I can use:
=sum(start:end!b2)

And drag the real sheets into and out of that "sandwich" to play what-if games.

 

[bambi4thomas]

No, I double checked all the "B2"s and they've all got numbers in them
I also tried the start and end sheets and it still came up with
#VALUE.
I've also tried a couple formulas with OFFSET and INDIRECT and I can't
get them to work how I want, but I've never used those before so maybe
I'm not doing it right?

 

[Dave Peterson]

Insert those start and end worksheets (for the sandwich).

And put your formula in a worksheet that's outside the sandwich.

Now start dragging each worksheet outside the sandwich. Check to see what the
formula evaluates to each time you drag a worksheet out.

When the formula stops giving an error, then you've found the problem.

If the formula gives an error and there are no more sheets between them, then
maybe you have hidden sheets that you didn't know about????