Help in Indirect and Relative Formula ?

General discussion on OpenOffice Calc and its spreadsheets.

Help in Indirect and Relative Formula ?

Postby FoxalTO » Fri Sep 12, 2014 5:01 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,
Posts: 26
Joined: Tue Mar 25, 2014 6:25 am

Re: Help in Indirect and Relative Formula ?

Postby Keya_LOT » Fri Sep 12, 2014 11:26 am


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: Select all
=SUM( (INDIRECT("'"&$A$10&"'." & ADDRESS(ROW();COLUMN();4)) ) : (INDIRECT("'"&$A$11&"'." & ADDRESS(ROW();COLUMN();4) ) ) )

Hope this will help you.
Posts: 24
Joined: Tue Mar 25, 2014 6:35 am

Re: Help in Indirect and Relative Formula ?

Postby Elwood M. Walker » Fri Sep 12, 2014 12:15 pm


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:

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.

Elwood M. Walker
Posts: 33
Joined: Mon Mar 24, 2014 6:09 am

Return to Calc

Who is online

Users browsing this forum: No registered users and 1 guest