Put Sheet Name as a Variable in Count or CountIf?

[kreber]

Hello Everyone,
In a particular sheet, I am trying to see how many items there are in a different sheet for a particular month.

Currently I am using the following formula in each month's column:
=COUNT('SDG 2010'!I1:I1328) - (COUNTIF('SDG 2010'!I1:I1328, "<12/1/2010") + COUNTIF('SDG 2010'!I1:I1328,">12/31/2010"))

Obviously, the dates are different depending on for which month I am counting items. The above example would be for December (read it as "count everything, and subtract from that anything less than December 2010 plus anything greater than December 2010) = # of items dated in December 2010.

Even though my logic may not be the most efficient, my real issue is:
SDG 2010 is a sheetname with a space in it. I would like to be able to manually change the sheet's name in its tab to SDG 2011, SDG 2012, etc, each year, without having to update the long cell formula above for each month! I've tried using INDIRECT in various ways to no avail, but maybe your way will work, who knows?

Thanks all,
Kurt

 

[dave3009]

You can try using INDIRECT in the COUNT functions, like this

Excel Workbook
	C	D
1	Sheet2	2
Sheet3

Excel 2007

Cell Formulas
Range	Formula
D1	=COUNTA(INDIRECT(C1&"!A1:A2"))

 

[PATSYS]

To get the sheetname, a non VBA solution would be:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

Use that with the function INDIRECT (which you I assume you already know) and you will get your desired result.