Extracting And Calculating Types Of Leave Per Department
Nov 24, 2009
to calculate how many days of each different leave type have been taken for each Cost Code within an Excel 2003 spreadsheet. What adds to the problem is that the data extracted from our system comes to +/- than 50 000 lines. So doing this manually is out of the question.
I have attached a spreadsheet containing an extract of the information (DATA Tab) to better understand. Under the Summary Tab I have the format in which the information must be presented. For each Cost Code/Department I need to calculate how many e.g. Annual Leave/Sick Leave/Other Leave etc. was processed for each month from January to end of October.
Has anyone dealt with something similar? I would really appreciate any assistance as this problem is way above my Excel skills (not that it’s actually above average in any case... ;-)
I have a file that's almost 3000 rows of data I have attached a test file for the purpose of this request. I am trying to figure out the average days overdue per department whose due dates have not already passed.test (1).xlsx
I am producing a spreadsheet that will track returned defects. Column B is where I input the department variable. In a new table on the same sheet I need a formula that will count department names and give me a total for the returns for each department.
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
From the example data of my previous post i would like to continue further to sum all the amounts based on department.My requirement is to sum the amounts in the next column down the department column. i had splitted the actual data into two categories based on departments "CNN" & "CNN-IN".
I had many departments like this in my data which i want to display them in different categories based on departments and the sum of the amounts in the next below cell of department. i got an idea on this that solution can be achieved if i apply Filters & Auto filter.But i m not perfect with the code and syntax.
See attached.PMS 2.xlsx. I have a table with data on PMS sheet and would like to have something like the example on the Example sheet using data from the PMS sheet.
I have multiple departments and on each department we report the costs in four columns this month, budget, last month and last year. I have summed across the row to a hidden column and if the value is 0 I have a basic macro that "hides" the zero value rows. I understand I could further automate the process by having an "event" based in the spreadsheet which when the calculated values change the macro will run. This would mean when looking at the next department the zeros would automatically be hidden. I suspect I need a worksheet_calculate event but not been able to make it work.
I am trying to track metrics for a QC dept. Right now my sheet has 35 lines of data and a header row, for 36 lines total. This is my funtion that isn't quite right:
I have a workbook that has a list of different depts. I need to design a way to enter a login of sorts to the excel and allow the user of that department to edit their entries only and lock the rest of the entries for the rest of the departments and do not show them in the result list after the user logs in. Once all department manager fills in his/her entries, the administrator is notified via email.
I'm trying to get the number of items against a specific department which are contained in a sheet called "data", the sheet is contained in the same workbook.
There are named ranges for Department (text) Date_From & Date_To (in the format dd/mm/yyyy) Band (Numeric 1 to 3) Items (Numeric)
Using the following formula I get an #NUM error (using Excel2003)
I send out a mail template everyweek to all the dept. employees using outlook. I attach a excel sheet to the mail so the employees can type in their hours and then sent it back. Then i go in manually each sheet and copy the info in THE timesheet excel sheet and sent it to the HR. Any easy way automate this process? What id like is to send a mai lcontaining the excel sheet, the emlpoyees fill it and the data tranfers to a excel sheet...
I work at a cardboard manufacturing plant in the Q.A Department. Occasionally paper claims need to be raised due to the paper being faulty (out of spec.). I have attached a spreadsheet which at present works out the paper cost according to which paper grade is typed into a particular columb. But, paper price varies according to the width (i.e. Deckle) of the paper as well. And this is the variable which I have not been able to implement thus far.
The relevant columbs are 'D', 'E' & 'I' for the purposes of this formula... It should be noted that below this I have pasted in the paper pricing list... and that in general each paper grade has 3 different prices depending on the Deckle (i.e. paper width), so there are 3 possible prices which can be displayed depending on the Deckle inputted into columb 'E'.
I'm trying to analyse data and summarise it by date (greater than 60 days, and between 30 days and 60 days), by department using the first two digits of department codes, and where there are multi-line order numbers.
A1 - Date B1 - Receipt No. C1 - DEPT CODE D1 - NOMINAL CODE E1 - Reason F1 - Amount G1 - Running total (I've put a little formula in there to keep the balance - clever eh!)
So - this is fine - except I have to add the total amount for each department/nominal code each month.
What I'm doing at the moment is copying and pasting, sorting the sheet by:
Department Nominal Code Date
Then doing a manual add up of each and adding it to my month end report.
I'm really not an expert at all - but I thought there must be an easier way for me to do it. After inputting the petty cash there MUST be a way that it can automatically calculate it?
Or is this going to be a difficult thing to do requiring expertise in pivot tables etc?
I have the following formula which returns the number of asset breaks for each head of dept in sheet3 based on their teams in Sheet1 and then does a countif in >30_DAYS_ASSET in sheet2. In sheet 1 is a mapping table with range names "Teams2", "Teams" & "Managers". I now need the formula to also do a count on the unique numbers in sheet2 Column B, if there are duplicate numbers then that will be 1 asset break, so for example for for Joe plummer we can see there are duplicate case numbers so the formula in total should return 2.
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I have a dataset that has a good number of duplicates, many of which have more than 2 records.
In these sets of duplicates, there are two fields that have many combinations of values.
I would like to find a way to COUNT THE COMBINATIONS in all of the sets of duplicates, as in, every time there is a type of a certain combination of values, create a count for it:
12345 9 9 12345 9 0
123456 9 9 123456 9 0
(Total: 2) For 9/9 and 9/0
12344567 0 0 12344567 0 0 12344567 0 0
12344567 0 0 12344567 0 0 12344567 0 0
(Total: 2) For 0/0 3x
I have attached a listing of types of combinations that I found by scanning the database, but I don't know what formula or functions would do the trick. I don't think there is something in Subtotal or the Count functionality that would apply but I will fumble around.
I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.
some of the functions in this file, and the problem is that I have one bug I can't fix.
There are four sheets. The first two sheets contain different types of scores.
The fourth sheet ranks each of the different types of scores on both first sheets. The third sheet reports out on the bottom five scores in each category. If one of the scores is missing, the whole thing gets screwed up.
I have attached the file and removed a some of the scores to illustrate.
I want to sum the values of two userform text boxes however I assume they are stored as strings so I get 1 + 2 = 12 - how do you convert strings to integers in VBA? It seems as though it's different to VB where you'd just use convert.toInt16() etc
But when I executed the macro, it froze on this lines saying "Method of 'Cells' Global Failure. I need a way around this but I have no idea how at this point.
I want to have an array where each record contains a name and three numbers. My understanding is that Excel requires all element types within an array to be the same, and so I turned to a user-defined data type. But I don't seem to be able to create a dynamic array out of this type--when I try to ReDim Preserve it, I get an error saying that it's already defined. Can't I do that?
interpolating numbers in excel for an exponential function. I want to know how to be able to curve a curve in 4 different ways essentially the 2 that make up the two halves of a peak and the 2 that make up the two halves of a trough. I've tried using the LINEST function to create this interpolation and it's good to get 1 of the 4 curves I'm interested in. how to do the other 3? I'm also interested in knowing if these functions "whip the tail up" as in almost like how the tail end of a "sin" function flips back up. here it is.
[URL]
For this example of the exponential function, it creates the curve in one particular direction. I was wondering how I would do it to have it curve in another direction, for example I was testing the same function, but going downwards, and also having the tail curve back up (by hump I mean to simulate a gaussian distribution with the tails) how would I do that. seems like I now have part 1 of 4 aspects of the curve. here's the attached file to show what I mean. I want to get the hump going up and then sort of reflected along the y-axis and then similar features if I were to reflect the curves on the x axis. is it possible with the functions in excel? Linest test.xlsx
I am having trouble converting file formats. I would like to convert a.xlsx file to a .xls file. It is password protected and everything I have tried to use to convert the file has failed.
I would like to create a chart in vba which contains 2 sets of data, both using the same x axis. The first must be a line graph, the second a staggered bar chart. This must be represented on one chart with data labels. Also, how do I change the colour of the plots?
My question is about creating User-Defined Data Types. Is there ANY way possible i can create a User-Defined Data Type that declares a variable of another User-Defined Data Type instead of the Pre-Defined User Types like String, Integer, etc?
The following explains my problem in more detail.
I know to create a User-Defined Data Type at the top of the module before any procedures. Like this:
