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.
Example:
Bob
Bill
James
Kathy
Should show
Bob
Bill
then
James
Kathy
not
Bob
Space
Bill
James
How you can find the min with simpler ranges, but my equation doesn't work with what I've found so far. I want to skip the zeroes and blanks.
=IF(AC44>0,(MIN((FS13+FL13),(FS18+FL18),(FS23+FL23),(FS28+FL28),(FS$417+FL$417),
(FS$422+FL$422),(FS$427+FL$427),(FS$432+FL$432))) ,)
A00956
ABT
DUK
CASH-1
The above column is in a spreadsheet I am working on. I would like to write a formula that looks at this column and returns all of the cells that are not blank (ie: A00956, ABT, DUK and CASH-1) I don't know how to have the formula skip the blank cells (the cells are blank but not empty).
i have table in the left side and i want to get the results in the table in the right side skipping blanks row price with taking in consideration blanks are formula contain ""
[IMG][/IMG]
I'm looking for a formula for return a result only when one other criteria is met. I've looked at SUMPRODUCT and VLOOKUP as options but haven't been able to make them work. Here is what I need:
I want cell E33 to look up cell B33 in 'Test Result Table' and return the 12th column of information, but only when cell C33 and 3rd column in 'Test Result Table' match. If the first result isn't a match I want the lookup to skip it and return the first available match.
I don't want to add anything, I just need a match returned based on C33 and the 3rd column being equal (cell C33 is a VLOOKUP of the 3rd column in 'Test Result Table').
I am trying to create a weighted average which will skip any row when Column B say's "yes" and then if Column N contains, a 0, I would like that to be skipped as well. The below works for skipping any row with the word "Yes", but it still includes 0 in the weighted average. Also, let's say the Column N contains a word and 0's, how can I skip that?
=SUMPRODUCT(($B$13:$B$15="Yes")*($I$13:$I$15)*(N13:N15))/SUMIF($B$13:$B$15,"Yes",$I$13:$I$15)
I have a sheet that lists all of the suppliers that we use (A2:A10). I have code that pulls the total number of reject by month for each supplier (C2:C10). What I would like is a formula that takes those values for the supplier and places them next to each other in columns E (see E2:E6) and does the same with the total reject values next to the applicable supplier (see F2:F6) without all of the zeros.
I think it is some kind of index and match function but cannot figure it out. I have attached an example of what I am trying to achieve.
Also, if at all possible, I would like to know if there is a way to auto populate a chart with those values and not the blanks that are generated (row 7 down in my example) so that my chart does not contain a bunch of blank space.
How do you make a column automatically place items in alphabetical order?
Also, how do you make a column automatically put dates in order?
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies View RelatedI have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
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
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.
Schd_05_TEST.xlsx
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?
View 4 Replies View RelatedI 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.
From the attached data is it possible to create a formula to count the number of staff in post that are line managers?
View 7 Replies View RelatedI 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?
How to get the card# from staff ID as lookup value? ask.PNG excel question.xlsx
View 6 Replies View RelatedI 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?
Table width is from Column C - Column J.
It is a formatting problem based on an INDEX formula i believe, but I am unsure where to start.
I have added a template where I have shown examples of what i want to achieve, the explanation is a lot clearer.
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.
Any way that I can do this in Excel?
I want a formula to calculate the average number of staff working per week in the attached data.
View 9 Replies View RelatedI 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.
A sample of the data is as follows;
Staff No:
Arrival time
Processed time
Exit time
1
26/02/2013 07:13
26/02/2013 07:22
26/02/2013 13:52
[Code] ........
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!
I have an data in a columns. Here I need to count the non-blanks and blank records.
View 5 Replies View RelatedI 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%.
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
I would like to calculate staff meal deductions for each employee based on the number of hours they worked for that day.
I have the hours listed all on one page, per day, per employee.
I have the staff meal deductions range listed on another page in the workbook.
The range is as follows: *Please note anyone working over 8.00 hours is deducted a maximum of $2.40 for that shift/day.
Hours
Deduction
1.00
$0.30
[Code].....
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................