• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using a function across multiple worksheets

kevinonearth

kevinonearth

Member
Hi,

I want to do a calculation of leave days taken by employees across an entire year.

I have a workbook with 52 worksheets in it representing 52 weeks of the year.

Each employee has one workbook.

For each day of the week if an employee was not in work he was either on "Annual Leave", "Sick" or various other types of possibilities (there is a drop down menu)


I have a master sheet which is meant to calculate the amount of say "Annual Leave" days taken over the year for that employee.


I have been trying to use: =COUNTIF('04 January 2013:27 December 2013!'L9:L13,A3)


where A3 contains "Annual Leave"

when entering the formula I click on the worksheet entitled '04 January 2013' hold down the shift key and select the worksheet entitled '27 December 2013' and then click and drag L9:l13 (where user selects type of leave from drop down menu)


Basically I want to use countif across a range of worksheets


Can you help?

Thanks
 
Hi kevinonearth,


If I understand correctly, you want to calculate the annual leaves (total leaves) of an employee by referencing 52 sheets (1 for each wk). I fso, then do the following for 1 employee(you can replicate the same for other employees/ other workbooks):


List all the tab names rowwise in a sheet and define a name of that range as 'TabNames'(I have used this in my formula)


I assume, in each sheet,you have the leave data from row 1 to 100 (it surely be different in your case).


In a separate sheet.


at A1 try this:


=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!A1:A100"),"Annual Leave"))


(this formula will fetch number of times the annual leaves are entered across 52 sheets and return the count)


at B1 try this:


=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!A1:A100"),"Sick"))


Then A1+A2 would be the total leaves for that employee


Hope this helps.


Regards

Kaushik
 
Hi kevinonearth,


If I understand correctly, you want to calculate the annual leaves (total leaves) of an employee by referencing 52 sheets (1 for each wk). I fso, then do the following for 1 employee(you can replicate the same for other employees/ other workbooks):


List all the tab names rowwise in a sheet and define a name of that range as 'TabNames'(I have used this in my formula)


I assume, in each sheet,you have the leave data from row 1 to 100 (it surely be different in your case).


In a separate sheet.


at A1 try this:


=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!A1:A100"),"Annual Leave"))


(this formula will fetch number of times the annual leaves are entered across 52 sheets and return the count)


at B1 try this:


=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!A1:A100"),"Sick"))


Then A1+A2 would be the total annual leaves for that employee


Hope this helps.


Regards

Kaushik
 
Putting name of 52 tabs in a sheet will be cumbersome task. Follow this link to do it easily:

http://chandoo.org/forums/topic/addin-to-insert-name-of-all-sheets-in-one-worksheet
 
Hi Shailyog,


Thank you for sharing this. I did not look at this post before. Can u upload the addin in 2007 compatibility mode, because I do not have 2010 installed in my system.


kevinonearth:


As Shailyog rightfully mentioned that putting 52 worksheets name manually is really a cumbersome task (which I completely agree), you can try the following to list all worksheets name by using the formula without the help of macro:


Steps to follow:

Hit Ctrl + F3, New | Name : SheetsName


In Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

[Note:In my case it is workbook (1), but you may need to change the name of the workbook in parenthesis according to what you have]


In a separate worksheet at A1 write the following:

=IFERROR(INDEX(SheetsName,ROWS($A1:A$1)),"") and copy all the way down (the number of rows you copy down should be at least the number of sheets you have in the workbook


Then follow the rest of the actions(the sumproduct formula)in my earlier post to achieve your goal.


Regards,

Kaushik
 
@kaushik03


Thanks for the simple solution. Its really amazing. Can you please explain how GET."workbookname" part works in your formula.
 
Thanks for the response on this, but I'm not quite there yet


@kaushik

could you explain the syntax of: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")


my workbook is called "Flexi Time Sheet"


how does that fit in to the syntax?
 
@kaushik


I've managed to get all the worksheet names in a new sheet, but when I type:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!A1:A100"),"Annual Leave"))


I get a #REF error!


Any ideas, thanks for your patience with me on this
 
Hi Shailyog,


thanks for your response, but I'm still getting a #REF


Obviously I have modified the formula to reference the correct cells in my sheet

My Leave data starts in L9 and ends at L13 in each of the sheet

There are 5 relevant cells in each week (one for each working day)


But still I am getting a #REF
 
Yes I've just joined dropbox (this site is brilliant!!), here is the link to my file:


(I want to perform the calculation in the worksheet called calculate:)


https://dl.dropbox.com/u/90990975/Flexi%20Time%20Sheet.xlsm
 
Hi Kevin ,


The problem is your values in the TabNames sheet A1:A52 are all numeric ; convert them to text using the following formula :


=TEXT(A1,"dd mmmm yyyy")


Put this formula in B1 , and copy it down till B52.


Change the reference for the named range TabNames to B1:B52.


Now , your formula should work.


Narayan
 
Hi kevinonearth,


I would suggest you to convert the column B text formula to value. I mean to say select B1 to B52,copy the rage, press alt+e+s+v and OK. Now your Column B entries are converted to values.Now copy the entire range (B1:B52) and paste it in A1 to A52. Your formula at D4 of Calculate sheet will return 5.


It works for me.


Try and let me know if you still face any challenge.


Regards,

Kaushik
 
Hi Kaushik,

yes I've done exactly as you said, but still I get 0 !!!


I do not know what I've done incorrectly ?


see my latest attempt!:

https://dl.dropbox.com/u/90990975/Flexi%20Time%20Sheet.xlsm
 
Hi kevinonearth,


I could see you have written the following formula at B3 as follows:


=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames!A1&"'!A1:A52"),A3))


You have given the reference of A1:A52 where as your actual reference is L9:L13


And no need to concatenate as you did A1&"'!A1:A52


At D3 in calculate sheet you write the following formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!L9:L13"),"Annual Leave"))


Let me know in case of any further challenges...


Regards,

Kaushik
 
Hey, it works, that's amazing, Thanks to everyone for the assistance.

No doubt I'll be back with more questions in the future.

sorry for being slow, I'm learning!
 
Happy to hear that it works for you now.


I believe this is a great place for learning.


I have learnt a lot from this forum...specially looking at the posts of Luke, Narayan, Hui and always the great Chandoo.


You all are really geniuous ....helping me to learn the mystry of excel.


Regards,

Kaushik
 
You must log in or register to reply here.
Back
Top