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,