Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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 On Jul 6, 8:46 pm, wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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. wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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???? wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |