COUNTIFS question

[gchandler]

I use COUNTIFS functions heavily at my job and i know there has to be a way to do what i want to do. When i use the COUNTIFS function i normally reference an entire separate Tab from the Tab my actual function is located. My Tabs that i pull from are normally named by the month of data that im working with. For instance, if im working on May data, my tab would be named "May data" and my function would look like =COUNTIFS('May data'........). I normally have 20-30 functions on one page pulling from that same separate tab so every month it is a pain to have to go back and change every single one of the formulas to reflect the change in tab name. For instance when June rolls along, ill create a tab named "June data" to pull from and have to go back and manually change every formula from =COUNTIFS('May data'...) to =COUNTIFS('June data'....). Is there a way to write my Tab name in a cell that i can reference to in my formula? That way when i change the cell it will automatically update all of my formulas? Ive tried and have had no luck whatsoever. Any help or direction would be greatly appreciated.

 

[gaz_chops]

One quick and easy way.... on the sheet with all the formulas, hit ctrl F, enter May, select replace enter June, select replace all. Done.

 

[XOR LX]

Hi.

With your month in B1 exactly as it appears on the tab, e.g.:

=COUNTIFS(INDIRECT("'"&B1&" data'!A:A"),"A")

Regards