# Making A Roster

Mar 30, 2007

I have been asked to make a formula that sums up the hours in a week and then tells me whether I am below or over the budgeted hours for that week!

I cant get it to work since I cant figure out how to have the same formula in all the cell that sums up early, mid and late shifts, ie 06-14, 14-22, 22-06.

The graveyard shift is a problem there since I get negative hours.

Further more, I need to have the formula ignore cells to be added up if it has letters in it, we write an R for Rostered day off.

## Formula For Costing Roster

Aug 6, 2008

Been trying to come up with a formula that I can use in cells to cost a roster.
What I want to be able to do is just input start times into cells and the hours worked is automatically costed out in another cell. The trouble I have been having is that different start times attract a shift allowance eg. before 05:00 you get a 20% loading for the whole shift, between 07:00 and 19:00 is 10% and any finishing time after 19:00 is 15%, don't know how to write the formula to do this.

## To Set Up A Web Query That Gets The Player Roster

Mar 13, 2007

I'm trying to set up a web query that gets the Player Roster from Indians.com and one column is giving me trouble.

On the web site, this column is the player's height, listed as, for example, 6-2.

When the web query import is complete, this column displays a date instead of the height.... so 6-2 comes in as 2-June.

How do I massage this column to display the height and not a date?

## Rotate The Names In The Roster

May 28, 2007

Need excel formula (not a marco) to rotate the names in the roster.

## Holiday Roster Templates

Aug 19, 2007

where I might find some?

## Employee Shift Roster Template

Nov 19, 2008

Any non-commercial roster template that would suit 7 day/24 hour/4 person shifts?

Currently we use a totally manual spreadsheet which looks like a Rubik's Cube and does not offer any form of automation in order to speed up entry adn ensure no doubling-up or missing shifts.

## Calculating Total Number Of Hrs In A Roster

Jan 2, 2007

I am working on this for two days , but I got stuck on the last step. I have a roster for about 35 employees. Calculating the daily hrs was not a problem. But I am doing the roster for one week. And I want employee wise total of hrs worked. I am quite confused as the "sum" formula works for some totals and for others it does not, although all the cells are in the right format. I tried to change the "result" cell to "number" and multiply by 24 to get the hr total as a number, but it does not work.

for example "SUMIF(E1:E57,"rafik",H1:H57)" ( this is the formula for calculating hrs for "rafik" on monday. the result cell is in "hh:mm" format and gives me the right total.

Likewise upto sunday the totals are right. What I want to do is calculate the total number of hrs from mon to sun. This seems to be impossible. the formula =SUM(H60:AL60) in a dd:mm format does not work, even =SUM(H60:AL60)*24 in a "number format" does not work.

I have tried "excel help" , tried to change the format but nothing works. The result should be 52 hrs and I cant get it no matter what I do.

## Rotational Roster For Groups Of People

Jun 10, 2009

I manage an emergency response team which consists of around 50 people, made of 5 different teams and these folks need to take part in emergency exercise to keep their skills up. I'm currently building a training/exercise/ roster database in Excel and would like to add a function that tells me who in each team is due to take part in the next exercise. For info: In the actual workbook, I have names defined on the team members and using vlookup to populate the roster via a drop down list.

To explain the attached example; once the person who is at the top of the list (Name1) has done their exercise, they move to the bottom of the list. Then over time, as the others also take part in execises, Name1 is eventually is back at the top of the list the and it is their turn again (this is quite often known as a Squash Ladder, here in the UK). Is there a way I can vba this process via a button or even better (!) by a drop down list?

## Random Assignment To Work Roster

Jan 28, 2008

i was asked to make a fair rota and i found this 1 on this website its useful but i need to add to it. if you download this work rota you will understand what i mean
http://www.ozgrid.com/forum/attachme...0&d=1154640274. i need to find out what the formula/macro is used in this work rota ...its exactly what i need but i need to add saturday and sunday to this rota and more duty columns as their is more employees and when i click the button it randomply places employees in different positions.

## How To Know Available Seats Left For Contact Center Roster

Feb 5, 2014

I would like to know that if i have seat count available according to floor and weekly off are planned according to team, also seat allocations is given floor wise, however i want to know how many seats are available on each floor or any given day.

I have attached excel sheet : Seat Allocation.xlsx

## FIFO Roster Calendar Identifying Same Days Off

May 10, 2014

Make an interactive calendar in excel. I would like to have 3 sheets. One were I can enter the names and corresponding reoccurring roster, that would be days on days off, a second sheet that does all the calcs etc and returns days of which all or most of the people are going to have off at the same time, so we can organise things in advance and maybe a third sheet that shows an actual graphical display of this information like a traditional calendar.

## Time Format - Add Hours In Total At The End Of Roster

Mar 26, 2013

When I enter L into B3 I want B3 to show a message.

i.e. B3 is blank, I enter L into B3, it the changes L to 0645 - 1515.

And it needs to be in time format so at the end of the roster i can add how many hours in total.

## Finding Duplicates And Highlight Enteries In A Roster

Sep 4, 2008

I have a roster for a large group in excel and would like to have an easy way to highlight if there are duplicate entries in the roster as we are merging multiple smaller lists together.

## Work Rota/Roster Calculating Hours

Aug 11, 2004

I am currently trying to create a rota / rosta for work which will show people what hours they are required to work, in a format similar to below:

NAME | SAT | SUN | MON | TUE |etc >
Person A| 8:00 - 17:00 | 13:00 - 22:00 | OFF | 8:00 - 13:00|
Person B| OFF | 17:00 - 22:00 | 8:00 - 17:00| OFF |

This is simple enough to create but I also need Excel to calculate how many hours each person is working in a week and the total number of hours used per day and per week. I found out that I can do this using a formula to work out hours and minutes between two times. However what I would also like Excel to do is deduct the unpaid breaks which the person is entitled to, however this depends on the times that they work, i.e. in the example above Person A would be entitled to a 1 hour unpaid lunch on Saturday, a half hour unpaid tea on Sunday and no unpaid break on Tuesday. Therefore 1 hour would be deducted off the Saturday total hours, 30 minutes would be deducted from the Sunday total hours and nothing taken off the Tuesday.

## Come Up With Automated Excel Table That Can Be Used To Create Car Park Roster?

Feb 7, 2014

I am trying to comeup with an automated excel table that can be used to create a carpark roster.

The carpark has 6 slots for whch I need to roster 9 staff on a weekly rotating basis. This needs to be done in an equitable manner. For example, at the end of a certain period of time, all staff memebers should have had equal chance and on the other hand we shouldnt have instance where a staff member goes two consecutive weeks without a car slot.

how i can optimally use excel to acheve the same?

## Generating A Repeating List Of Names From An Ever Changing Roster.

May 2, 2006

I have a workbook where Sheet 1 Col A is a list of dates. Sheet 2 Col A is a relatively short list of constantly changing names. I need to put this list of names in a repeating fashion in Sheet 1 Col B. Ideally I would like for Sheet 1 to regenerate Col B each time the roster list is changed.

## Making A Map

Feb 3, 2010

I am making a simple map in excel. I have my floor plan on sheet 1 and the location of product on the floor on sheet 2.

Currently I have some formulas on sheet 1 that link to some sales data on sheet 2, but what I would really like to be able to do is use the search function on page 1 and it give me the location of the products in a given area.

The data is broken up into group location on the second sheet.

## Making Lists...

Feb 6, 2010

A Names
B Streets
C Cities
D Phone no.

If I write a name in D1 I want to get the corresponding street, city and phone no. in E1, F1 and G1.

I also want to copy the formula downwards let's say ten times so I can make a list.

If I for example just want to make a list of three names (D1-D3) I want the rest of the fields to stay blank.

Aug 2, 2007

I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.

I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?

## Making A Calculator From Vb.net To Vba

Feb 11, 2008

i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;

## Making A Button

Mar 14, 2007

I have a spread sheet that tracks progress reports that are due every 90 days. I have a due date colum that will automatically get high lighted if the 90 days have passed to inform me or my staff that the report is late. I would like to have a colum with a button in each row that allows the reader of the spreadsheet to press "YES" for that accont's progress report having been turn in. When the "YES" button is pressed the date due date column advances to the next 90 day due date and the highlight would turn off.

I used the conditional format to change the due date column to Yellow if =TODAY() is less then or equal to the due date. But I can figure out a way to select something and turn the highlight off and advance the due date to the next 90 day due date.

## Making A Barlist

Aug 20, 2008

............Beer........ Total
Stefan:....1........... 0,70€

Now i need a button that when i click on the name the amount 1 gets 2,3,4,5,6, ect and that it calculates the price up as a total

## Making A Report

Jun 6, 2006

1- I 'm working with a table wit many Sheets.

I want to make a Report Sheet with selective ranges(cells)
the selective cells should paste as link ( in order to be updated cells)

2- then how to Zip this worksheet to zip file ( to save it)

3- If I have a Macro and i want to perform this macro to all sheet except one sheet how to that ?

## Making Squares

Jun 15, 2006

I would like to:

1-Make Rows & columns 1/4" X 1/4"

2-Be able to enter dimensions into an input box that would then draw a square(or rectagle) with the inputed dimensions in INCHES.

## Making Cell Mandatory

Aug 21, 2012

In Column B of my spreadsheet i have a drop down that only allows users to enter "Yes" or "No"

Is it possible to make Column C a required field if "No" is selected in column B.

So basically if cell b4 is "No" then i want to make it manditory that cell C4 is filled out with a reason why.

Same thing for cell b5 then i would like cell c5 filled out before they continue using the sheet. etc.

## Making Dropbox From Datatable?

May 6, 2014

I am a Coach who will be using this data table to call pitches via a catcher's wristband. The band is obviously completed below; however, I need to create a separate sheet where the numbers correlate to the pitches that are being called. How it works: everything in the table(s) with a dash is a pitch i.e. 2-1, 1-3, 1-U, etc. The numbers on the columns and row are what is relayed to the catcher to call the corresponding pitch. For example, I would signal is 113 (first number = row second number = column). 113 is 4-1. I need a way/ formula to where I can see which numbers are to be relayed in for each pitch. It would possible look like 2-1: 101, 411, 422, 501, etc.

Mar 28, 2014

I built what I can only describe as a fantastic spreadsheet. It wasn't until after that I realized it is 123 MB in size, but even for the complexities within it, it still feels like that is quite large.

I can't share the file due to proprietary reasons, but any general tips as to things I can do to make it smaller (and therefore faster to run).

I tried saving it as a binary spreadsheet and that dropped it to about 80 MB, but I don't really know what that does and if it will impact the sheet performance in any way.

## Function Making Worksheet

Apr 24, 2008

But we have just started and i havent gotten all the functions to work yet.

Make function that tests if D9 is larger then 100 and I9 is less then 50. If true "Kontroll" if not "OK" should be written.

Also, if "Kontroll" shows up it should have a dark green "OK " should just be black

Its roughly translated from norwegian. If you need some more information I will try to provide it

## Making Sure Cell Has 'J' Prefix

Nov 23, 2008

i have a cell "h23" that must always start with a "j" or a "J"
the trouble is some of my users are only putting in the number
ie 2345 when it should be j2345 or J2345

## Making One Worksheet Talk To Another

Dec 8, 2008

I know I posted this same topic about a week ago but I went through the first 20 pages on the forum and couldt find it, i tried the search option and nothing came up. I have several worksheets in the same workbook. When I enter a number in one cell in work sheet A, I want the same number to show up in a given cell on work sheet B. How do I do this? I can make words move from one shet to the next but numbers dont seem to be the same.