I have a table on a sheet called Contracted Staff. Table has staff names down the side and along the top is training needed.
Training H & S. fire. General training
Lucy. 5/4/12
Jeff. 5/4/12
Michael.
Dawn. 5/4/12
I would like on a training summary sheet to tell me who hasn't completed the training. I know I can use auto filter to select blanks to get who hasn't done it. However I want to print every training and list on the same sheet.
Is there a way I can count the number of different people in a column in any one day. The same persons name may appear more than once on the same day. My table is as follows:
Column A has my list of dates running from A2 to A500 Column E has my list of workers running from A2 to A500
My table of results is in a different workbook but basically what I need is this:
Nov 1 - Count how many people worked on 1st November Nov 2 - As above Nov 3 - As Above Nov 4 - As Above Nov 5 - As Above etc
The Master list: Sheet1, Column C has unique numbers, i.e, 0608-211-1093-2.
(can have thousands of entries)
The Comparison list: Sheet2, Column C will have duplicates of some of Sheet1, Column C numbers.
(Sheet2, Column C will have the numbers but other columns will have different data than Sheet1 and may have a few hundred entries or less)
I need a macro that will that will compare the two sheets for duplicates based on Column C and then write the entire row of Sheet1 with the duplicate number to a new Sheet3.
I need counting the number of staff within a time period. Unfortunately the schedule structure does not allow application of the simple solutions I've found regarding this which results in this being more complicated. My example is attached. My start and end time are in the same cell. I have used the Left() and Right() functions to manipulate the times. I am unable to count the number of occurrences of between the start/stop times of all my employees in the array. The formula also needs to take into account a manually entered time on the actual day.
Basically:
00:00 if F9:F42 = time is TRUE and I9:I42 = "" then count occurrences of 00:00 in scheduled times F9:F42 + if F9:F42 = time is FALSE, then if I9:I42 = time is TRUE then count occurrences of 00:00 in scheduled times I9:I42
01:00 02:00 " "
Even when I think I've gotten close the midnight time crossover keeps throwing off.
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this
1 - Yes 0 - No left blank - n/a
B3 counts the number of entries in the column B2 counts the number of 1's in the column B28 = B2/B3
Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?
Currently I'm working on functionality in my staff holiday spreadsheet and I've come up with this amazing idea for a calendar overview for month by month.Now the sheets I currently have is summary and jan to dec.
I plan on adding the sheets Jan (Cal) to Dec (Cal) now this is when my idea gets nifty - The calendar view will import the information from the month table and will show it on the calendar.
For example X has 1 holiday in July for the 20th - this will show up on the calendar as "x on holiday", I'm still working with spreadsheets so I'm not sure if excel is powerful enough to do this - but is this actually possible?
I have a table that will have a drop down list in all of the cells in column F that are in the table. (I think I have the drop down list figured out
I want to create a button that makes it so every time a new row is needed for new stock entries etc, the user just has to press the button and it should just create a new row within the table. Simples .
Is this achievable? And if it is, what is the best way to go about it?
I have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
I have this calendar with a total of 31 staff assigned. I need to assign two staff to cover each day not grayed out utilizing the staff tab from the top (A1) to the last (A31). Need to go to next name if blank.
I have a sheet set up to record free pour tests for my bar team.
Column A has the date. Alternating columns from B (B..D..F.. etc) hold a drop down with the staff names Alternating columns from C (C..E..G.. etc) hold a drop down with either pass or fail as the result.
What I need to do is count the number of times a particular staff name appears, but more importantly how many times they pass or fail.
I can easily count the names, but how do I count if they have pass or failed?
I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
I have data showing when staff arrive at, enter after clearing security and then exit a building after completing their task. The data for each cell is a combined date and time as opposed to separate cells for date and time.
I am trying to ascertain how long our security staff take to clear persons waiting to enter the building and also how many people at any one time are actually in the building (after being processed through security).
I also need to ascertain if particular issues arise at certain days of the week (eg Mon morning) and so need to add a further column which shows the day of week using the date/time data.
I had thought about trying to display the data using a a stacked bar chart but I still need to look at any particular date/time and ascertain how many persons are in the building without manually counting the rows!
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list 2. Sheet2; how to remove "B000" or "B00" from the list 3. Sheet3; how to highlight difference or find out the difference between to lists
What I am trying to do here is to compare 2 lists in 2 different files, and when there is a match, then copy and paste the related cells of the matching name. Sorry if this sounds messy, perhaps the sample file I have attached can explain better.
Every month I get a new file in the format of “Data Source” sheet where the list of banks in column A and the figures in column M, AA and AB might change from month to month. For the sake of convenience, I put the source data as a different sheet instead of different file here.
I have an existing report template in the format of “Final report” sheet where basically I copy and paste the relevant cells according to the name of the banks.
I don’t think I can use Vlookup because the cells that I want to extract are not right beside the search criteria. If I’m wrong please correct me.
Anyway, assuming a macro is needed for this, I am wondering if I can create a macro, where it can search the list of banks in column A in “Data Source” sheet based on the list in column A in “Final Report” sheet, then copy the correct cells from column M, AA and AB and then paste them into the correct cells in columns B, E and H in “Final Report” worksheet?
Note that not all the banks in the “Final Report” sheet are in the “Data Source”, so for this example, row 4 for ABN Bank should remain blank after the search because it is not listed in the “Data Source”. The Data Source List might also change over time.
There is also this problem of the bank names from the “Data Source” sheet not being exactly the same as the existing list in “Final Report”. For example in this file, ANZ Bank in the other sheet have all the extra stuff behind, but we know it is the same bank.
I have a spreadsheet whereby process activities can be attributed to the different staff members who complete the activity. Each activity is on a different row and up to three staff can be assigned to each activity, so for example if a level one employee sorts the mail on four out of five days per week I would attribute 80% contribution to the task to the level one employee. If a supervisor sorts the mail on a Friday I would allocated a 20% contribution to that staff group.
My question is how do I prevent someone filling in the spreadsheet from allocating a combined contribution above 100% to any task. This could be for example either putting a number in greater than 100% into one of the allocation columns or a total across up to three different columns above 100%.
The columns where I assign the percentage contribution are E,H and K. I would like to generate an error which says something like 'Cumulative staff contribution per process activity cannot exceed 100%.
This is what I’ve been trying to do. There are 3 different work schedules for each employee. Each employee gets 2 days off in a row. At the top of the employee schedule worksheet, there are 3 schedules. The zeroes represents days off for that schedule and the 1’s represents days worked. Ok, Cells c7 thru c17 display total staff needed for that day Cells c8 thru c18 display average number of customer on a particular day Cells c9- 19 display number of needed based on a staff to customer ratio of 1 employee per 80 customers B11 displays that ratio
I need to make a comparison of two different staff to customer ratios. My goal is to find lowest total payroll while adequately staffing the store. In B13 I want to enter the daily salary amount for 1 employee at $10 hr/8 hrs per day
In A16 enter total payroll amount Total staff B7 times salary in B13
Use solver to find minimum payroll amount Target cell is total payroll found in A16 Target cell must be = to minimum value................
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.
Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.
I need to pull out daily staff location schedules from a 20wk cycling rota. and produce daily shedules for staff and more detailed for the wolves above. Attached file gives an idea of what I am trying to achieve.
I have 16 seperate titles and a different figure for each title.
However, these 16 titles are repeated hundreds of times, in different orders, down a column. I need to populate the list with the numbers that belong to each title.
I don't have v look up on my computer. I am useless at excel.
I have two separate lists, one in Column A and the other in Column B. Both lists are roughly 2,000 rows long. For the most part, the two lists are identical, but not quite.
I'm trying to determine what data is present in Column A that is absent from Column B, and vice versa. That's easy enough to do with COUNTIF (at least that's what I've tried), but to complicate matters, duplicate entries appear in both column A and column B.
For example, in column A, the word "Electric" appears 13 times...but in column B, it only appears 8. I need to know these differences in a quick and easily readable way (remember, the spreadsheet is roughly 2,000 rows).
I've made a 40 page list on Excel, On the very top of this list is my titles of whats is in the collums. How do I repeat this through out, so it appears on the top of every page.
