Dynamic range for filter function

Question

I have the following formula:

=iferror(sum(filter('November 2017'!$H:$H;'November 2017'!$D:$D=$B3)); 0)

Which I use to categorize expenses in a Summary sheet: $B3 would be for example Rent, and this would pick all expenses in the November 2017 sheet marked as Rent, and would compute the sum of it. This works fine.

But this is cumbersome: I have 20 expense categories that I am tracking, and now I want to create the corresponding entries for December 2017, which forces me to go through all the formulas and manually replace November 2017 -> December 2017.

Is it possible to tell filter that the range of operation is specified in certain cell? This way I would just update that single cell (in the top of the December column for example), and all formula would use the right range.

Answer 1

You might use INDIRECT within a query, say:

=query(indirect($B4&"!D:H");"select sum(H) where D ='"&B$3&"' label sum(H) ''")

but really you seem to have backed yourself into a corner. You appear to be trying to analyse data that has already been analysed. A more conventional solution may be to collect all the data together in one place (sheet) and then perform all the analysis from there, say with a pivot table.

B4 would hold November 2017 (as text), B5 say December 2017 and you would copy down the above formula (and across for other categories).