I have a spreadsheet with three sheets. Two are called 2012 and 2011 and have a bunch of similar data. The last sheet does comparisons between the data.
To be able to choose year, I'm using a cell (D1) where I can I can write either 2011 or 2012. The formulas then use the INDIRECT function to include this cell as part of the reference.
INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!F:F")
This is not a pretty solution and makes the formula quite long and complex.
=IFERROR(SUM(FILTER( INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!M:M") ; (INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!B:B")=$A4)+(INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!B:B")=$A5)+(INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!B:B")=$A6)+(INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!B:B")=$A7)+(INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!B:B")=$A8); MONTH(INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!D:D"))=$B$1 ; INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!F:F")=D$3));0)
Is there a better way of doing this?
I've tried to create a separate spreadsheet for the calculations sheet and importing (IMPORTRANGE) the data from the two sheets together on one sheet with VMERGE (custom function from the script gallery) but there is quite a lot of of data in these two sheets and the import takes a long time. Any changes (like changing year) also take a long time to recalculate.
Database functions tend to be cleaner when doing this kind of thing.
https://support.google.com/docs/bin/static.py?hl=en&topic=25273&page=table.cs&tab=1368827
Database functions take a while to learn, but they are powerful.
Or
You could put INDIRECT(CHAR(39)&$D$1&CHAR(39)&"!B:B") in a cell on its own.