Counting number of leave days over the year

[kevinonearth]

Hello,
I have a workbook with 53 work sheets in it.
52 of these work sheets are hours worked and leave days taken for every week of the year.
The 53rd sheet calculates the amount of days worked, leave days, sick days etc over the year by using:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!K9:K13"),A3))

but this formula is not working!
I attach the file here:
https://dl.dropboxusercontent.com/u/90990975/Timesheet 2014.xlsm

Can you help?

 

[ismailzkhan]

Hi,

Your file is password protected please check!!

 

[kevinonearth]

Silly of me!
Sorry password now removed!
https://dl.dropboxusercontent.com/u/90990975/Timesheet 2014.xlsm

 

[Xiq]

Hi there,

Only looked at your named ranges, but I noticed alot of REF# values... including the named range "TabNames" you use in your formula.

 

[Xiq]

Hi kevinonearth,

To follow up my previous post, if you reference "TabNames" to the numbers(!) in your "TabNames"-sheet... keep in mind that you first convert these numbers to text. I.g. by using something like this: =TEXT(A1,"dd mmmm yyyy")

 

[IzharAhmed]

Sir kindly tell me how to change a 1 cell border.. please reply me im waiting..

 

[Somendra Misra]

@IzharAhmed

If answer in calculate sheet for IN is 2. Then use below formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&TEXT(TabNames,"dd mmmm yyyyy")&"'!k9:k13"),A3))

Regards!

 

[kevinonearth]

Hi,
thanks all for replies. It is working now
Thanks again