Calc cannot adjust references that are inside quotes--they're just text data, which Calc ignores. So the "B1" reference will never change when you copy the formula.

You might try something like this:

=SUM(INDIRECT(ADDRESS(1;COLUMN();4;1;$A$10)) : INDIRECT(ADDRESS(1;COLUMN();4;1;$A$11)))

That will add cells in row 1 of the current column, for all sheets in the provided sheet range. Since the "current column" will change as you copy the formula, it will act as a relative reference.

Thanks,

Statistics: Posted by Elwood M. Walker — Fri Sep 12, 2014 12:15 pm

]]>

Since B1 is in quotes, it is a text string and will not be adjusted like a cell reference would be. Here is a solution:

- Code:
`=SUM( (INDIRECT("'"&$A$10&"'." & ADDRESS(ROW();COLUMN();4)) ) : (INDIRECT("'"&$A$11&"'." & ADDRESS(ROW();COLUMN();4) ) ) )`

Hope this will help you.

Thanks,

Statistics: Posted by Keya_LOT — Fri Sep 12, 2014 11:26 am

]]>

I'm wondering if anyone can help me out with a “Relative” Formula. I have a document with hundreds of sheets, all sheet are from the same template I made. Sheet one is used as a totals page, all the other sheets are used as say a daily sales page. Cell A1 has how many apples I sold (Say 5), cell B1 has how many banana’s I sold (Say 5), C1 how many cucumber's (Say 5) etc... So if each day I sold 5 banana's , all the sheets will have 5 in cell B1.

On Sheet 1 (Totals page) in cell B1 I have the formula:

=SUM( (INDIRECT("'"&$A$10&"'.B1") ) : (INDIRECT("'"&$A$11&"'.B1") ) )

In Cell A10 I have an input field where I type which Sheet to start adding a total, and cell A11 to input where I want it to end. So in this case:

Cell A10 has “Sheet2” typed in it, and cell A11 has “Sheet5” typed in it.

With the formula above in cell B1 I get the desired result of 20. (5 for sheets 2,3,4,5)

Now the problem I'm having is, if I copy that formula into cell C1 to get the result for “Cucumbers sold on those same sheets ( Lets say I sold 1 cucumber on each of those sheets, the result I'm looking for is 4. ) However the formula stays absolute to cell B1.

Anyone know how to change the B1 reference in the formula to a relative reference so when I copy to C1 the formula changes accordingly?

thanks in advance,

Statistics: Posted by FoxalTO — Fri Sep 12, 2014 5:01 am

]]>