I have a formula that creates a summary based on a date range. The formula works fine, but I would like to use a cell say G1 to generate the name of the worksheet so that each year we can add a new worksheet and by changing G1 to that worksheet's name the summary is redirected to that worksheet rather than having to go in and edit each formula.
The formula used at the minute is:
=SUMIFS('2015'!V:V,'2015'!A:A,">=" &A2,'2015'!A:A,"<=" &A3,'2015'!D:D,A5)
I want to replace 2015 with a cell reference where I can type in 2015 or what ever the year is I have named that worksheet.
I have tried using =INDIRECT but it just gets lost in the formula somehow.
Please try:
=SUMIFS(INDIRECT(G1&"!V:V"),INDIRECT(G1&"!A:A"),">=" &A2,INDIRECT(G1&"!A:A"),"<=" &A3,INDIRECT(G1&"!D:D"),A5)
where G1 holds the sheet name.