Automate Sum Formulas

[LadyDi]

Is there a way to automate sum formulas? I currently has a table that sums data from a larger table by quarter. I have totals and averages for the order volume for each quarter. Right now, I am typing in each formula (=Sum(C10:E10)). I have recently been asked to show the order volume for previous quarters (i.e. =SUM (C9:E9)). I set up a table with column references and row references for each quarter and am trying to use that to create my sum formulas, but it isn't working. I would like to get it, so that all I have to do is update the row reference and all my formulas will update (i.e. when a new year is added). The formula I tried, that isn't working is =SUM(Address(S4, AC4, 1, 1)&":"&Address(S4, AD4, 1, 1)). I thought using the Address forumla would provide the cell references for the Sum formula, but it returns the #VALUE! error. Is there a way to get this to work? Any assistance you can provide would be greatly appreciated.

 

[NBVC]

Try using INDIRECT()

e.g.

=SUM(INDIRECT(AC4&S4&":"&AD4&S4))

IF AC4 and AD4 are holding "C" and "E", respectively to indicate Columns and S4 is holding a number to indicate the row...

 

[LadyDi]

That works pefectly. Thank you so much! That formula is going to save me so much time.