Using Indirect to Maintain Year-to-Date Sums from Another Worksheet

Question

First time poster. I'm trying to sum cells from an earlier dated worksheet in order to maintain year-to-date counts for comparison. The way my workbook is setup now, I have one year's worth of data on each worksheet and the data used are monthly reports. The monthly reports are pasted into my 'Template' worksheet. The data from these is then organized into a time series on separate worksheets, and the name for each of the worksheets is the year for which the data corresponds. When adding new data throughout the course of a year, I was wanting to pull in year-to-date totals from the previous year in order to compare similar time frames. This is what my formula currently looks like.

=SUM(INDIRECT("'"&LEFT($A13,4)&"'!C17:OFFSET(C$16,MONTH(RIGHT(Template!C$3,10)))"))

LEFT($A13,4) references the year I want pulled in for the current worksheet, and MONTH(RIGHT(Template!C$3,10)) identifies the number of the month for the current report. So, for May 2015, Left($A13, 4) would return "2014" and MONTH(RIGHT(Template!C$3,10)) would return a value of 5.

As (I hope) is evident, I'm attempting to begin my summation at the beginning of the annual series found on the previous year's worksheet, and then use OFFSET to pull in only the months that end with the current report month for this year to do a year-to-date change. This is returning #REF! in Excel when I attempt to do this. First impressions make is seem that INDIRECT is not recognizing OFFSET as a function, and is treating it as text. Is there any way around this?

I see something similar was posted earlier, but was unable to translate to my situation. Any help would be greatly appreciated.

Answer 1

Hard to replicate without the data, so take this for what it's worth...

Breaking your formula down (one distinct argument per line), we have

=SUM(
  INDIRECT(
    "'"
    &
    LEFT($A13,4)
    &
    "'!C17:OFFSET(C$16,MONTH(RIGHT(Template!C$3,10)))"
  )
)

meaning the line beginning "'!C17:OFFSET(... is likely being treated as text by Excel, since it is enclosed within quotation marks. You probably want

=SUM(
  INDIRECT(
    "'"
    &
    LEFT($A13,4)
    &
    "'!"
    &
    C17
    &
    ":"
    &
    OFFSET(C$16,MONTH(RIGHT(Template!C$3,10)))
  )
)

in order to feed all the syntax (', !, :, etc.) to INDIRECT as strings and all the cell values as formula results.

Answer 2

Thank you for taking the time to post an answer to my question. I think I figured out a much more workable solution to my problem than I had previously imagined though. Instead of having the only the annual worksheets with just one year's worth of data, I am instead having the reports added on the end of a new worksheet with the entire time series in a sheet titled "Raw Data" with column "A" of the new worksheet containing a month-ending date. This is what my formula looks like for adding new reports to the 'Raw Data' sheet.

=IF(AND(YEAR($A2)=YEAR(Template!$F$4), MONTH('Raw Data'!$A2)=MONTH(Template!$F$4)),Template!C$35," ")

Template!$F$4 references the date found on the report.

My annual worksheets then reference the 'Raw Data' sheet in order to eliminate the need to use the INDIRECT function. For maintaing year-to-date sums for the data found in column "C" of "Raw Data", here is my formula.

=SUM(INDEX('Raw Data'!C:C,(MATCH(EOMONTH($A17,-12),'Raw Data'!$A:$A,0))):OFFSET(INDEX('Raw Data'!C:C,(MATCH(EOMONTH($A17,-13),'Raw Data'!$A:$A,0))),MONTH(RIGHT(Template!$F$4,10)),0))

EOMONTH($A17, -12) is set up to always reference January of the previous year. This is then is OFFSET by the number of the month found on the report referenced by MONTH(RIGHT(Template!$F$4,10)) and summed.