Shift Roster on Excel, pls help

Who can help me to auto generate roster on excel, it is very
complicate...pls help...

1)	bill date (5, 7, 11, 14, 17, 21, 25 and end of month) need to 6 day
3 night, but if Saturday, Sunday or Public Holiday need to 2 day 2
night
2)	non bill date only need to 6 day 2 night staff
3)	non bill date if Saturday, Sunday or Public Holiday need to 2 day
and 1 night
4)	if on Friday, need to 5 day 3 night
5)	on shift staff, each staff need to have 1 double off on roster and
one Saturday off, two Sun off another 2 off on weekend, they cannot
continuous more then 5 days work and cannot last day on NIGHT shift,
next day on DAY shift
6)	if Staff A on night duty, and that night only 2 staff or 1 staff,
it may be additional add 1 more staff on night shift too

10 Staff in a Team, 4 staff is not on shift, that mean only on duty at
day and day off on Sat, Sun and Public Holiday; another 6 staff need
to on shift duty, how can I to be fair to automatically generate a
roster? A roster basic on 4 week, start on Monday.

More information, all staff must be on duty job (except one of
department head, he is not on shift duty, only duty at day and day off
on Sat, Sun and Public Holiday)

A = day
N = night
A/R = duty 1
A/E = duty 2
A/M = duty 3 (except on Sat, Sun and Public Holiday)
0
beancurd
2/27/2010 4:36:49 PM
excel.programming 6508 articles. 2 followers. Follow

17 Replies
1706 Views

Similar Articles

[PageSpeed] 49

I started the code below but need more info to continue.  I don't
understadn the Term "Shift staff".  Also, is there a minimum number of
hours each employee need to work?  I don't understand the supervisors
shcedule.  Does the 10 employees include the supervisor or is the
supervisor in addition to the 10 other employees.  Also how many
supervisors are there.


the code requeire the workbook to have at least on sheet Names
"Holidays".  On this sheet put the word Holiday in cell A1.  Then list
all the holidays in a date format starting in cell A2.

The code will delete all sheets except the sheet named Holidays and
create a 12 month clendar with the number of employees needed each
shift.

I'm thinking of rotating the employees but don't have all the
requirements.  Usually you would have an employee work 5/6 days one
shift then have two days off and going to next shift.




VBA Code:
--------------------
  

  
Const NumberOfStaff = 10
  Const FirstShiftRow = 6
  Const SkipRows = 3
  Sub GenerateCalendar()
  
  'set up rows on worksheet where to start each shift
  SecondShiftRow = FirstShiftRow + NumberOfStaff + SkipRows
  
  'code assigns Employees in Order
  EmployeeCount = 1
  
  'Get holiday range
  With Sheets("Holidays")
  LastRow = .Range("A" & Rows.Count).End(xlUp).Row
  Set HolidayRange = .Range("A2:A" & LastRow)
  End With
  
  MyYearStr = InputBox("Enter Year : ")
  MyYear = Val(MyYearStr)
  
  'Delete All sheets except Holiday
  
  Application.DisplayAlerts = False
  For ShtCount = Sheets.Count To 1 Step -1
  If Sheets(ShtCount).Name <> "Holidays" Then
  Sheets(ShtCount).Delete
  End If
  Next ShtCount
  Application.DisplayAlerts = True
  
  For MonthCount = 1 To 12
  'start calendar on column 2
  ColCount = 2
  
  'Get LastDay of the Month as a date
  'the last day of the month is the day before
  'the 1st day of the next month
  LastDay = DateSerial(MyYear, MonthCount + 1, 1) - 1
  
  'put name of month on worksheet tab
  Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
  With NewSht
  .Name = MonthName(MonthCount, abbreviate:=True)
  
  .Range("A3") = "Bill Date"
  .Range("A4") = "Holiday"
  .Range("A" & FirstShiftRow) = "First Shift Number Needed"
  .Range("A" & SecondShiftRow) = "Second Shift Number Needed"
  
  'put days of month on column Header
  For DayCount = 1 To Day(LastDay)
  MyDate = DateSerial(MyYear, MonthCount, DayCount)
  .Cells(1, ColCount) = Day(MyDate)
  .Cells(2, ColCount) = Format(MyDate, "DDD")
  
  'check if Bill Date
  Select Case Day(MyDate)
  Case 5, 7, 11, 14, 17, 21, 25, Day(LastDay)
  BillDate = True
  Case Else
  BillDate = False
  End Select
  .Cells(3, ColCount) = BillDate
  
  'check if date is a holiday
  Holiday = False
  For Each MyHoliday In HolidayRange
  If MyDate = MyHoliday Then
  Holiday = True
  .Cells(4, ColCount) = "Yes"
  Exit For
  End If
  
  Next MyHoliday
  If Weekday(MyDate, vbSunday) = vbSaturday Or _
  Weekday(MyDate, vbSunday) = vbSunday Or _
  Holiday = True Then
  
  If BillDate = True Then
  AM_Needed = 2
  PM_Needed = 2
  Else
  AM_Needed = 2
  PM_Needed = 1
  End If
  
  Else
  
  If BillDate = True Then
  AM_Needed = 6
  PM_Needed = 3
  Else
  If Weekday(MyDate, vbSunday) = vbFriday Then
  AM_Needed = 5
  PM_Needed = 3
  Else
  AM_Needed = 6
  PM_Needed = 2
  End If
  End If
  
  End If
  
  .Cells(FirstShiftRow, ColCount) = AM_Needed
  .Cells(SecondShiftRow, ColCount) = PM_Needed
  
  
  
  ColCount = ColCount + 1
  Next DayCount
  .Columns.AutoFit
  End With
  
  
  Next MonthCount
  
  End Sub
  
  
--------------------


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
2/28/2010 4:40:11 AM
Hi Joel,

sorry for mis-information and very thanks you your big help.

On Shift staff (6 staff), total day off is 7 days, it must be include
1 time 2 continuous day off, 1 day off on Saturday, one day off on
Sunday, another 3 day off on weekday; Also they cannot continuous more
then 5 days work and cannot last day on NIGHT shift, next day on DAY
shift

10 employees include the supervisor, 4 staff (include the supervisor)
is office hour work 9:00-18:30; another 6 staff is Shift staff, that
mean they need to report duty on DAY shift (9:00-18:30) or NIGHT shift
(12:00-21:30)

How about the job duties schedule, how can I to be fair......

all staff must be on duty job (exclude one of supervisor)

A = day
N = night
N/R = night job duty
A/R = day job duty 1
A/E = day job duty 2
A/M = day job duty 3 (NOT include on Sat, Sun and Public Holiday)
0
beancurd
2/28/2010 12:33:42 PM
Hi Joel,

Can you send me your file to me, because I got error message on VBA.
Thanks!
0
beancurd
2/28/2010 12:46:52 PM
I updated my code.  The code below starts by asigning holidays only. 
See if this looks fair.  I setup a queue to select employee for each
shift.  I assigned a point value depending on the type of day/shift the
employee is working.  so I start out by going through the entire
calendar year and each holiday I choose the employees to work wih the
lowest score which I get from the queue.  The scores in the queue can be
changed if the assinments don't look correct. 

Job duties may be assigned random based on the people working. I'm also
thinking if a person works a holiday on either Saturday or Sunday they
should work both weekend dates.

Let me know what your holidays are so I'm woking with the same schedule
you have.  My next task would be to assign weekends.  I would check if
he person is working a holiday in the middle of the week and not assign
the person to work either the weekend before or the weekend after the
holiday.

Is it better for a person to have both Saturday and Sunday off together
or have a person work either Saturday or Sunday.

I would then assign the 7 days off for each person.  Is the 7 days off
for the month?

Look at the code and se if there is any problems with my logic for
assinments.  Based on my scoring system I will assign night before day
shift base on the lowest score in the queue.  Then fill in the day
schedule with the remaining workers.




VBA Code:
--------------------
  

  
Const NumberOfStaff = 10
  Const FirstShiftRow = 6
  Const SkipRows = 3
  
  Const WorkDayValue = 1
  Const PMBonus = 0.2
  Const WeekendBonus = 0.5
  Const HolidayBonus = 1
  
  Enum WorkType
  NotScheduled
  Work
  Off
  WorkAM
  WorkPM
  End Enum
  
  
  Type CalendarDay
  AM As Integer
  PM As Integer
  Holiday As Boolean
  BillDate As Boolean
  Employee(NumberOfStaff) As WorkType
  End Type
  
  Type EmployeeScore
  Number As Integer
  Score As Single
  End Type
  
  
  '366 days to include leap years
  Dim WorkYear(0 To 365) As CalendarDay
  Dim Queue(0 To (NumberOfStaff - 1)) As EmployeeScore
  Sub MakeSchedule()
  
  MyYearStr = InputBox("Enter Year : ")
  MyYear = Val(MyYearStr)
  
  'Delete All sheets except Holiday
  
  Application.DisplayAlerts = False
  For ShtCount = Sheets.Count To 1 Step -1
  If Sheets(ShtCount).Name <> "Holidays" Then
  Sheets(ShtCount).Delete
  End If
  Next ShtCount
  Application.DisplayAlerts = True
  
  'initialize employee queue
  For EmployeeCount = 0 To (NumberOfStaff - 1)
  Queue(EmployeeCount).Number = EmployeeCount + 1
  Queue(EmployeeCount).Score = 0
  Next EmployeeCount
  
  Call MakeCalendar(MyYear)
  Call AssignHolidays(MyYear)
  Call OutputCalendar(MyYear)
  
  End Sub
  Sub AssignHolidays(MyYear)
  
  FirstDay = DateSerial(MyYear, 1, 1)
  LastDay = DateSerial(MyYear + 1, 1, 1) - 1
  
  DayCount = FirstDay
  DayOfYear = 0
  Do While DayCount <= LastDay
  If WorkYear(DayOfYear).Holiday = True Then
  Call SortQueue
  
  DayScore = WorkDayValue + HolidayBonus
  
  If Weekday(DayCount, vbSunday) = vbSaturday Or _
  Weekday(DayCount, vbSunday) = vbSunday Then
  
  DayScore = DayScore + WeekendBonus
  End If
  
  QueCount = 0
  'assign employee to work based on order in queue
  
  'Assign AM work
  For EmployeeCount = 1 To WorkYear(DayOfYear).AM
  EmployeeNumber = Queue(QueCount).Number
  'add the day value to employee score
  Queue(QueCount).Score = Queue(QueCount).Score + _
  DayScore
  WorkYear(DayOfYear).Employee(EmployeeNumber) = WorkAM
  QueCount = QueCount + 1
  Next EmployeeCount
  
  'Assign PM work
  For EmployeeCount = 1 To WorkYear(DayOfYear).AM
  EmployeeNumber = Queue(QueCount).Number
  'add the day value to employee score
  Queue(QueCount).Score = Queue(QueCount).Score + _
  DayScore + PMBonus
  WorkYear(DayOfYear).Employee(EmployeeNumber) = WorkPM
  QueCount = QueCount + 1
  Next EmployeeCount
  
  End If
  DayOfYear = DayOfYear + 1
  DayCount = DayCount + 1
  Loop
  
  
  End Sub
  
  Sub SortQueue()
  For i = 0 To (NumberOfStaff - 2)
  For j = (i + 1) To (NumberOfStaff - 1)
  If Queue(i).Score > Queue(j).Score Then
  'swap employees
  temp = Queue(i).Number
  Queue(i).Number = Queue(j).Number
  Queue(j).Number = temp
  
  temp = Queue(i).Score
  Queue(i).Score = Queue(j).Score
  Queue(j).Score = temp
  
  End If
  Next j
  Next i
  
  End Sub
  
  Sub MakeCalendar(MyYear)
  
  'Get holiday range
  With Sheets("Holidays")
  LastRow = .Range("A" & Rows.Count).End(xlUp).Row
  Set HolidayRange = .Range("A2:A" & LastRow)
  End With
  
  FirstDay = DateSerial(MyYear, 1, 1)
  LastDay = DateSerial(MyYear + 1, 1, 1) - 1
  
  DayCount = FirstDay
  DayOfYear = 0
  Do While DayCount <= LastDay
  
  'Get LastDay of the Month as a date
  'the last day of the month is the day before
  'the 1st day of the next month
  LastDayofMonth = DateSerial(MyYear, _
  Month(DayCount) + 1, 1) - 1
  
  'check if Bill Date
  Select Case Day(DayCount)
  Case 5, 7, 11, 14, 17, 21, 25, Day(LastDayofMonth)
  BillDate = True
  Case Else
  BillDate = False
  End Select
  WorkYear(DayOfYear).BillDate = BillDate
  
  'check if date is a holiday
  Holiday = False
  For Each MyHoliday In HolidayRange
  If DayCount = MyHoliday Then
  Holiday = True
  Exit For
  End If
  
  Next MyHoliday
  WorkYear(DayOfYear).Holiday = Holiday
  
  If Weekday(DayCount, vbSunday) = vbSaturday Or _
  Weekday(DayCount, vbSunday) = vbSunday Or _
  Holiday = True Then
  
  If BillDate = True Then
  AM_Needed = 2
  PM_Needed = 2
  Else
  AM_Needed = 2
  PM_Needed = 1
  End If
  
  Else
  
  If BillDate = True Then
  AM_Needed = 6
  PM_Needed = 3
  Else
  If Weekday(DayCount, vbSunday) = vbFriday Then
  AM_Needed = 5
  PM_Needed = 3
  Else
  AM_Needed = 6
  PM_Needed = 2
  End If
  End If
  
  End If
  WorkYear(DayOfYear).AM = AM_Needed
  WorkYear(DayOfYear).PM = PM_Needed
  
  For EmployeeCount = 0 To (NumberOfStaff - 1)
  WorkYear(DayOfYear).Employee(EmployeeCount) = NotScheduled
  Next EmployeeCount
  
  DayOfYear = DayOfYear + 1
  DayCount = DayCount + 1
  Loop
  
  End Sub
  
  Sub OutputCalendar(MyYear)
  
  FirstDay = DateSerial(MyYear, 1, 1)
  LastDay = DateSerial(MyYear + 1, 1, 1) - 1
  
  CurrentMonth = 0
  DayOfYear = 0
  
  DayCount = FirstDay
  Do While DayCount <= LastDay
  If Month(DayCount) <> CurrentMonth Then
  'if not first month autformat columns
  If CurrentMonth <> 0 Then
  MonthSht.Columns.AutoFit
  End If
  
  'add newsheet
  'put name of month on worksheet tab
  Set MonthSht = Sheets.Add(after:=Sheets(Sheets.Count))
  CurrentMonth = CurrentMonth + 1
  
  With MonthSht
  .Name = MonthName(CurrentMonth, abbreviate:=True)
  
  .Range("A3") = "Bill Date"
  .Range("A4") = "Holiday"
  .Range("A" & FirstShiftRow) = "First Shift Number Needed"
  .Range("A" & FirstShiftRow + 1) = "Second Shift Number Needed"
  
  'Put emplyee number in row header
  For EmployeeCount = 1 To NumberOfStaff
  .Range("A" & _
  (FirstShiftRow + EmployeeCount + SkipRows)) = _
  "Employee " & EmployeeCount
  
  Next EmployeeCount
  
  End With
  
  ColCount = 2
  End If
  
  With MonthSht
  'put days of month on column Header
  .Cells(1, ColCount) = Day(DayCount)
  .Cells(2, ColCount) = Format(DayCount, "DDD")
  .Cells(3, ColCount) = WorkYear(DayOfYear).BillDate
  .Cells(4, ColCount) = WorkYear(DayOfYear).Holiday
  
  .Cells(FirstShiftRow, ColCount) = WorkYear(DayOfYear).AM
  .Cells(FirstShiftRow + 1, ColCount) = WorkYear(DayOfYear).PM
  
  For EmployeeCount = 0 To (NumberOfStaff - 1)
  Select Case WorkYear(DayOfYear).Employee(EmployeeCount)
  Case NotScheduled
  WorkTypeStr = ""
  Case Work
  WorkTypeStr = "Work"
  Case Off
  WorkTypeStr = "Off"
  Case WorkAM
  WorkTypeStr = "Work AM"
  Case WorkPM
  WorkTypeStr = "Work PM"
  Case Else
  WorkTypeStr = "Error"
  End Select
  'don't output anything if not schedule
  If Len(WorkTypeStr) > 0 Then
  .Cells( _
  (FirstShiftRow + EmployeeCount + SkipRows), ColCount) = _
  WorkTypeStr
  End If
  Next EmployeeCount
  
  End With
  
  ColCount = ColCount + 1
  DayOfYear = DayOfYear + 1
  DayCount = DayCount + 1
  Loop
  
  'format columns in last month
  MonthSht.Columns.AutoFit
  
  End Sub
  
--------------------


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
2/28/2010 1:42:23 PM
And one Big problem, some staff have special request on roster, (e.g.
Staff A want to DAY shift on 13/5/2010, Staff B want to NIGHT shift on
14/05/2010, Staff C want to OFF on 15/5/2010, sometimes have special
request on annual leave too).  If Public holiday, will be get
additional one day compensate off , (e.g. on Apr have one day public
holiday, so that the total day off should be 7 days + 1 day compensate
off)=85.

This is too hard and very complicate roster=85I=92m very headache to
handle this roster on fair=85
0
beancurd
2/28/2010 1:45:15 PM
Hi Joel,

Shown error message when I run the VBA, "Compile error: Cannot define
a Public user-defined type within an object module"

Pls Help~~
0
beancurd
2/28/2010 2:54:23 PM
Make sure you copied all the code.  Also the code should go into a
Module.  Insert a module from the VBa menu Insert then add code to
module.


The fairest approach is to make a schedule for the entire year which is
random like what I've done so far.  I could do a monthly schedule which
goes back into the previous days worked a learns which days a person has
already worked.  That get more complicated.  The schedule requirements
are already complicated.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
2/28/2010 3:46:49 PM
Thanks you very much, Joel.
But after run the marco, I find that missing generate job duties on
schedule and the Employee 10 is missing shift duty on Public
Holiday......Pls give me a big hand~~
0
beancurd
3/4/2010 4:08:55 PM
Thanks you very much, Joel.
But after run the marco, I find that missing generate job duties (A, A/
M, A/R, A/E, N and N/R)* on schedule and the Employee 10 is missing
shift duty on Public
Holiday......Also, find out the schedule if on public holiday but not
in bill date, it generate 4 staff on duty, but it should be only 2 AM
staff and 1 PM staff only.....Pls give me a big hand~~

* A only for Day shift
A/M only for Day shift but need to handling some Specify job duty1
A/R only for Day shift but need to handling some Specify job duty2
A/E only for Day shift but need to handling some Specify job duty3
N only for Night shift
N/R only for Night shift but need to handling some Specify job duty4
0
beancurd
3/4/2010 4:28:09 PM
Thanks you very much, Joel.
But after run the marco, I find that missing generate job duties (A,
A/
M, A/R, A/E, N and N/R)* on schedule and the Employee 10 is missing
shift duty on Public
Holiday......Also, find out the schedule if on public holiday but not
in bill date, it generate 4 staff on duty, but it should be only 2 AM
staff and 1 PM staff only.....Pls give me a big hand~~

* A only for Day shift
A/M only for Day shift but need to handling some Specify job duty1
A/R only for Day shift but need to handling some Specify job duty2
A/E only for Day shift but need to handling some Specify job duty3
N only for Night shift
N/R only for Night shift but need to handling some Specify job duty4


0
beancurd
3/4/2010 4:35:52 PM
I've been working on the code a little bit each day.  I expect to be
finish tonight.  I will check to make sure the problems you listed gets
fixed.

I'm generating the schedule in the following order

1) Holiday
2) Weekends - This is the most complicated sections.  I'm scheduling
Saturday and Sunday together.  I schedule on person on the AM shift for
both Saturday and Sunday and one person one the PM shift for both
Saturday and Sunday.  Then I schedule the remaining people trying to not
schedule the same person on more than one shift.  I also try not toe
schedule a person when they work a holiday from Wednesday to Tuesday
(with the weekend in the middle).  And I try not to schedule a person
who works Friday Night on a holiday on Saturday morning  then try not to
schedule a person who works on a Monday AM holiday on sunday night.

Meeting all these conditions and meeting the number of people you
required to work is impossible.  So there will be a few times when all
these conditions are not met.  Usually the some of people who are off
for a holiday will also have to work the weekend.

There is a trade off between the reuirement for people to have both
Saturday and Sunday Off and having people working both Saturday and
Sunday.  I don't schedule people to have off both Sturday and Sunday
since with holidays and billing cyles this is impossible.  Instead I
rotate the pople who have off both days so everybody eventually gets
both days off and equal number of times.  I do the same thing with
working only Saturday and working only Sunday.  So if you look at the
schedule on a monthly basis you may not see everybody getting off both
Saturday and Sunday together. But you will see the schedule rotating as
you go through the year.

3) I then schedule nights making sure pople don't work morre than 5
days in a row.  Try not to schedule people Friday night after I
scheduled them Saturday morning.  And I also try not to schedule people
a night before a holiday when they are work the next morning.


4) Finally I will schedule the day shift.  I will need to use one of
the shift workers on Friday to meet the demand for 5 people.


One question.  Is there a minimum number of hours each shift person
need to work?  My plan is to only schedule people to meet the scedule
requirements and not to schedule any more people than the requirements. 
I'm not sure if shift workers will get too many days off.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
3/4/2010 6:09:51 PM
Oh joel, you are so helpful and "Thanks you" x 100 million.
Day shift 9:00am - 18:30pm
Night shift 12:00pm - 21:30pm
0
beancurd
3/5/2010 5:21:03 PM
I got everything working last night but on the 78th day of the year the
macro couldn't fill a shift person because anybody available would of
worked for more than 5 days in a row.  What I'm going to do is highlight
the cell in the worksheet Red and let the code continue.  Then see if
there is a way to easily fix the problem.  I think it occured on a
Friday day shift where I already schedule the person to work the
weekend.  I have a few algoritms I thinking of using to stop this from
happening.  Right now my queing system is comparing looking for the
person who worked the fewest number of days.  Instead if I calculate the
number of days a person is working an individual week (including the
weekends and holidays I already scheduled) the problem may not occur.

I'm also getting a number of cases where a person is working a night
shift and is also scheduled to work the next day shift.  I want to see
the number of times these issue occurs and see if I can easily fix the
problem.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]

0
joel
3/5/2010 5:40:57 PM
Hi joel,

did you set the marco, these are 4 staff (include the supervisor) is
working on Day shift only (office hour 9:00-18:30) and day off on Sat,
Sun and Public holiday?
0
beancurd
3/5/2010 6:01:34 PM
I wasn't the Staff, it was the shift people.  The Staff people are
simple.  the problem is if you need 6 people on a billing Friday it is
difficult to find a 2 people from the shift workers who haven't worked
or will work 5 days in a row and are not working that night.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=183222

[url=&quot;http://www.thecodecage.com/forumz/&quot;]Excel Live Chat[/url]

0
joel
3/5/2010 6:57:13 PM
humm....is it no way to solve this problem?
if the shift staff can work 6 days, can help on this situation?  but
need to better fewest time for the shift staff working on 6 days and
must to be fair.

0
beancurd
3/6/2010 8:06:30 AM
Oh no, anyone can help me?? Joel~~where are you??
0
beancurd
3/16/2010 3:46:05 PM
Reply:

Similar Artilces:

Excel Filters
I need to know how to make my filter work when my sheet is protected. My filter works fine as long as my sheet is NOT protected, but when I protect my sheet, my filter won't work. Is there any way to make a filter work when the sheet is protected? I have Excel 2000. I need help from anybody. Thanks see ans in worksheetfunctions. -- Don Guillett SalesAid Software donaldb@281.com "Harold H" <anonymous@discussions.microsoft.com> wrote in message news:1f32d01c456f2$c082d2d0$a001280a@phx.gbl... > I need to know how to make my filter work when my sheet is > protected....

shift
This is my problem, let say that I got 3 shifts Colomn A fill with Employer ID_No Colomn B fill with Date Colomn C fill with Shift Colomn D fill with In_Time Colomn E fill with Out_Time Colomn F fill with Absen or Present Colomn G fill with Overtime Shit I 08:00 AM to 04:00 PM ; normal work hour, next hour will be overtime Shit II 04:00 PM to 12:00/00.00 AM ; normal work hour, next hour will be overtime Shit III 00:00 AM to 08:00 AM ; normal work hour, next hour will be overtime I need the formula to calculate hour in COlomn F & G thank for any help ...

Excel XML
We have created a web application that allows users to generate Excel documents. We use Excel XML to create the document and allow users to open or save the document. This all works fine. The problem occurs after the created file has been opened on the users' computer and stays open for a while. Every now and then Excel crashes. Excel also crashes when the user tries to Save As.. the document. Does anyone have any suggestions as to why that occurs. ...

How can I delete rows from Pivot Tables in Excel 2000 as in 97
I was able to delete multiple unwanted rows from Excel 97 Pivot Tables. Now I find I can only delete rows individually by untagging them in Excel 2000 Pivot Table. This is far too time consuming... Does anyone have a faster Solution ...

Opening Excel spreadsheets via explorer
Hello! I'm hoping the experts here can help me with this quirky issue. I have a user who when she double clicks on a spreadsheet from her My Documents folder or from an email or such, Excel 2007 opens yet will not open the document itself. If you click around long enough it usually will open, or if you go to the open dialog you can open it that way. Here's what I have tried - under Excel Options/Advanced I've tried both unchecking and checking the option to ignore other applications that use DDE with no luck. Also have tried modifying how the sheets open using this - Applicatio...

Roster / Staff Allocation
Hey All Just wondering if on ur travel's if you have come across, a template that could be used when rostering and allocating staff within either a retail (Checkout operators) or a aged care facility. Any assistanced would be greatful. Thanks Aaron -- KingAaron ------------------------------------------------------------------------ KingAaron's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37444 View this thread: http://www.excelforum.com/showthread.php?threadid=571114 http://office.microsoft.com/search/redir.aspx?assetid=TC060891341033&QueryID=U...

excel save #2
i would like to creat a macro that will save a file automatically when pressed but without deleting the previous one. can anyone help -- Lins ------------------------------------------------------------------------ Lins's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24223 View this thread: http://www.excelforum.com/showthread.php?threadid=378390 What would you like to happen - always get the Save As dialog? To change the name of the previously saved version of the workbook? Something different? In article <Lins.1qio6c_1118585102.3933@excelforum-nosp...

Weird shifting
I have two odd things happening in my document that I don't *think* are related, but who knows...? I'm using Word 2007 and I have a template that I created specifically for this document. It is a very convoluted template based on obsolete military style guides that I have no say in. I have 7 heading levels and each heading level has numbered procedural steps. Each heading level indents one step from the next higher heading, so by the time I get to the highest heading level my left indent is about 3". Crazy, but like I said, I have no say in the format. Anyhoo... w...

Information Source, Excel 2007 versus 2002 Major Differences Dec0
I'm an intermidiate VBA programmer. I've been developing and addin using excel 2002. Am going to 2007 soon, and would like to pointed to a source where I can see what VBA changes, and others, MUST be made. For example, using Walkenbach's book I search for "help" to insert an app menu before it, there is no "help" in the excel 2007 main menu. Thanks. -- Neal Z ...

Saving Excel 2000 Workbooks as Web Page
Hi all, I currently have about 1000 excel workbooks that I wish to save as webpages. Is there any easy way to do this without opening and saving each workbook indiviually? If so, any pointers would be greatly appreciated... Thanks in advance.... ...

Blank areas near Excel pie charts when printed
I have XP SP3 and Office 2007 and HP LaserJet M3035 printer with PCL6 driver. When I print a presentation or slide some of the slides with linked Excel pie charts will make text, borders, etc. that are near it disappear. So far, the only way I've seen to reproduce the event is to have Office 2007 SP2 and a PCL driver installed. It can be printed with High Quality and with or without the various options listed in PowerPoint Options -> Advanced -> Print. The workarounds I've found are to uninstall Office 2007 SP2 and go back to SP1 or use the printers PostScript dri...

Why does Excel close me out...
Why does Excel close me out after working only 3 sheets? When I start a new page, it will only allow me up to the 3 default sheets... Joseph Please don't post more than once. Especially after only a few minutes. Your 19 minutes earlier post was answered. Gord Dibben Excel MVP On Tue, 14 Sep 2004 11:02:37 -0700, "Joseph" <anonymous@discussions.microsoft.com> wrote: >Why does Excel close me out after working only 3 sheets? >When I start a new page, it will only allow me up to the >3 default sheets... ...

PLEASE HELP
Someone tried to help me but I didn't had any results... I'm working on a two worksheets. The formula is as follows: =VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BE;57;0) I want to copy the formula to one cell on the right and turns as follows: =VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BF;57;0) My problem is that the coll index number does not change... that means I have to change it manually for 50 collumns. PLEASE HELP $A92 is an absolute column, A92 is relative and changes when copi...

Excel
I have numbers with 4 leading zeros and want to drop 3 - i've tried custom 0##### but the numbers seem to be formatted so that they won't change. How can I drop those 3 leading zeros I would suggest posting in an Excel group where you will find more people dealing with the program. I don't know what catagory your question would fall into, but you can start in: microsoft.public.excel.worksheet.functions or microsoft.public.excel.templates This group for Operating System questions instead of the Application. On 2010-06-08 15:43, Cheri wrote: > I have ...

Multiple Cell Values, please help
Hi! I have a problem and i would be very happy if someone could resolve it. This is my question to you: a would like to have one table in one sheet that has, let's say 20 columns and 80 rows. And now somewhere on the sheet there is something like a autofilter function in which I choose which date i want. And now i choose one date and this table is blank and i write things in the table,now i choose some other date and again i write things in this table and so on. So I want to have like 20,30,40... tables at the same spot in one sheet and just to pick one date and the table has values for t...

excel 97 opening excel 2003
Hi, Is it possible to open with Excel 97 an Excel 2003 file which include a choice list ? Great thanks It should be--but it kind of depends on what you mean by a "choice list." If you mean Data|Validation or a dropdown from the Forms toolbar or a combobox from the Control toolbox toolbar, then yes, it should work ok. Gerard Marin wrote: > > Hi, > > Is it possible to open with Excel 97 an Excel 2003 file which > include a choice list ? > > Great thanks -- Dave Peterson Thanks for your reply. Into Excel 2003 : It is simply "right clic" on...

Does Excel 2003 have CDF feature in its histogram plotting function?
Hi all, Does Excel 2003 have CDF feature in its histogram plotting function? Here CDF means "Cumulative Probability Density Function", We know that histogram is actually discretized version of PDF, which stands for "Probability Density Function". But where can I find the CDF plotting function in Excel? Thanks a lot cfman I think you will have to write a function to integrate the pdf yourself. Within a spreadsheet you can use the FREQUENCY() function on very small partitions/bins to create the data via which you chart the pdf, then doing a cumulative sum of the r...

Excel: Reminder/Pop-up message
Hi Guru out there, I would just like to ask for all of your help. I actually know basic from Excel - I'm using version 2002. I have an excel sheet, that has a column of " Termination Date " its date format was 20081112(yyyy/mm/dd). Can you help me, on how to put a reminder or pop-up message that I have termination date on or before 20081130 (Nov.30, 2008)? Also, if possible to turn to color RED - once it detected that its date are for future dates? let say, I have termination date for Nov.30 and Dec. 10.. all future dates are automatically turn to color RED? Hope tha...

hiding columns, error message says cannot shift objects off sheet
Hiding columns on a spreadsheet ( which we do frequently). Error message says: 'cannot shift objects off spreadsheet'. WHat does this mean and how do I work around it? Thanks! hi, excel thinks that there is data in column IV. excel by default cannot shift data off the sheet ie beyound column IV or below row 65536. solution. highlight all the column to the right of your data and hit the delete key. that should fix it. >-----Original Message----- >Hiding columns on a spreadsheet ( which we do frequently). Error message >says: 'cannot shift objects off spreadsheet&...

Help website veering to the left what to do?
Is there a way to make it so the webpage always centers itself in the middle of the browser, in stead of looking like it's being pulled to the left if the browser window is opened up large? Drew. Publisher left justifies the web pages by default, and if you are using 2003 or 2007, there is no way to center the web pages at this point. The best you can do is simulate centering. Reference: Understanding background padding in a Publisher web (aka white space) : http://msmvps.com/blogs/dbartosik/archive/2006/01/07/80563.aspx If you are using Pub 2000 or 2002 there is a way to cent...

Charts in Excel
Is there a way to customize a grid line on one of the axes? I am using excel 2000. All of the regular gridlines in a set must be formatted the same. But if you want to add a different one, or put them at irregular locations, try the technique on this page: http://www.geocities.com/jonpeltier/Excel/Charts/ArbitraryGridlines.html - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ boyesee wrote: > Is there a way to customize a grid line on one of the > axes? I am using excel 2000. Right click on the axis and the select &...

Database records from excel templates
How do i get data entered into a template form such as invoice to update a database. This used to work in previous versions of excel and is mentioned in help but no where does it tell you how to set the form to do this. There's information on the Microsoft site for the template wizard for Excel 2002: http://www.microsoft.com/downloads/details.aspx?FamilyID=E01EA3D8-094D-4ED1-88DA-F4B8B2AD28BD&displaylang=en BeeJay wrote: > How do i get data entered into a template form such as invoice to update a > database. This used to work in previous versions of excel and is mentione...

Excel #10
How to paste formated sheets to new file sheet? Formated sheet has artwork (logo) and formulas that should carry as values only Right-click the sheet tab and choose "Move or Copy." Choose "new book", and check "Create a copy," then click OK. You will be in the new book. Click the rectangle at the intersection of row and column headers, to select all, then Edit> copy followed by Edit> Paste Special> Values. Save the new file. "Mister B" wrote: > How to paste formated sheets to new file sheet? Formated sheet has artwork > (logo) an...

Help 05-09-10
I have a toshiba satellite a100. When I turn it on it goes to a blue screen that say "a problem has been detected and windows has been shut down to prevent damage to your computer. If this is the first time you've seen this stop error screen, restart your computer. If this screen appears again, follow these steps: disable or uninstall any anti-virus, disk fragmentation or backup utilities. Check your hard drive configuration, and check for any updated drivers. Run chkdsk /f to check for hard drive corruption, and then restart you computer. Technical information: ***stop: 0...

Excel Queries with subqueries and parameters
Hello All, I'm a developer newly aquainted to the beauty of excel reports via pivot tables. So here's my delimma, I have a sheet in which I execute a query. This query uses three parameters such as start month, end month, and year to retrieve data from the database. This works great. As soon as I add a subquery to the SQL statement that excel is editing, it tells me that the query is not valid and that all of the excel parameters contained within the query are bad. If I take those parameters out, (for a test to determine if it thinks the sql syntax is bad), and replace them with real...