using CountIF to goto range in the correct month sheet?

[lpking2005]

Is it possible to tell a formula bar to pull a range from the correct month tab in another workbook?

I have this in a table of cells:

=COUNTIF('[403 CTI 2011.xls]AUG-11'!$I$5:$I$42,"")

As you can see, it goes to the "AUG-11" sheet because of the range i selected.
I need it to always read the correct data for the correct month.

Does anyone know if this is possible or a workaround?

Thannks!

 

[jimrward]

take a look at the INDIRECT function

it will take me a while to work out the correct syntax

 

[jimrward]

=COUNTIF(INDIRECT("'[403 CTI 2011.xls]'" & A1 & "!$I$5:$I$42"),"")


where A1 holds AUG-11 or whatever, i think i have it correct give it a try

 

[lpking2005]

Thanks for the reply, but i still cannot get this to work.

I will explain what i need a bit more, in case its my fault.

In workbook1, i have a group of cells which need this formula in each cell:

[/url]
cti cells by lpking2005, on Flickr[/IMG]

each cell turns either Red or Green based on how many cells have been filled out on the "403 CTI 2011" workbook.

The "403 CTI 2011" workbook is yearly based, so every sheet is named by month/year. eg. "AUG-11", "SEP-11" and so on.

There is a cell on each sheet which says the name of the sheet also, if that helps. Plus i have coded it to always open the workbook at the correct month.

[/url] cti sheet by lpking2005, on Flickr[/IMG]

I hope this explains it more clearly