Creat a spreedsheet with moving rows?

[Mikeyfyr]

I am firefighter who has been given a project that I hoping someone here
can help me with. I have worked with Microsoft Access quite a bit, but
I think Excel is better for this project. I have made an inventory
progam in Access that seems fairly complicated, to me at least. So my
BC was hoping I could put something together for an overtime staffing
list. I do not know VBA. Below is brief summary of what I am trying
to accomplish.

We have 3 separate personnel lists from which to hire overtime when
nessasry, one for each rank of Captain, Operator, and Firefighter.
People are listed in order of when they last accumulated 24 or more
hours of overtime. The most recent person to accumulate 24 hours of
overtime will be the last person on the list. When an overtime coverage
is needed the BC (battalion chief) will go down the list and offer
overtime to people that are available to take overtime. The person
first on the list may be working that day or on annual leave or for
what ever reason can not work that day. So the BC continues down the
list. When he finds a person to accept the overtime, it may be a full
shift, 24 hours, in which case they will go to the bottom of the list
from where ever their current position on the list is. If the overtime
is a partial, say 10 hours, then they will not move from their current
position unless they have previously accumulated 14 hours or more and
therefore putting them over 24 hours. At which point they will be moved
to the bottom of the list. Once you are moved to the bottom all hours
are reset to 0.

This a can be a very lenghthy proccess for the BC. We currenty are
using a card file but mistakes can be made trying to track hours and
positions.

This is an example:

Name "Accrued Hours" "Accepted Hours"

Smith " 0 " " 0"
Johnson "10 " " 0"
Jones " 0 " " 0"
White " 0 " " 0"

When the BC offers someone hours (Accepted Hours), they will be added
to (Accrued Hours). Once Accrued Hours have equal or exceeded 24 that
person will be rotated to the bottom of the list. If Smith accepts a
14 hour overtime he does not move because his Accured hours will then
be 14. If Johnson accepts 14 hours he will move to the bottom of the
list and then his Accured hours will be rest to zero.

I know I am asking an impossible question. If anyone can help I will
really appreciate the assistance. Thanks for your time.

Mike Tonner
Firefighter
Sparks NV

 

[Ken Russell]

Why not insert an extra column for Total Hours, ie Accrued Hours + Accepted
Hours. You can then do a sort on Total hours in ascending order.
--
Ken Russell

| I am firefighter who has been given a project that I hoping someone here
| can help me with. I have worked with Microsoft Access quite a bit, but
| I think Excel is better for this project. I have made an inventory
| progam in Access that seems fairly complicated, to me at least. So my
| BC was hoping I could put something together for an overtime staffing
| list. I do not know VBA. Below is brief summary of what I am trying
| to accomplish.
|
| We have 3 separate personnel lists from which to hire overtime when
| nessasry, one for each rank of Captain, Operator, and Firefighter.
| People are listed in order of when they last accumulated 24 or more
| hours of overtime. The most recent person to accumulate 24 hours of
| overtime will be the last person on the list. When an overtime coverage
| is needed the BC (battalion chief) will go down the list and offer
| overtime to people that are available to take overtime. The person
| first on the list may be working that day or on annual leave or for
| what ever reason can not work that day. So the BC continues down the
| list. When he finds a person to accept the overtime, it may be a full
| shift, 24 hours, in which case they will go to the bottom of the list
| from where ever their current position on the list is. If the overtime
| is a partial, say 10 hours, then they will not move from their current
| position unless they have previously accumulated 14 hours or more and
| therefore putting them over 24 hours. At which point they will be moved
| to the bottom of the list. Once you are moved to the bottom all hours
| are reset to 0.
|
| This a can be a very lenghthy proccess for the BC. We currenty are
| using a card file but mistakes can be made trying to track hours and
| positions.
|
| This is an example:
|
| Name "Accrued Hours" "Accepted Hours"
|
| Smith " 0 " " 0"
| Johnson "10 " " 0"
| Jones " 0 " " 0"
| White " 0 " " 0"
|
| When the BC offers someone hours (Accepted Hours), they will be added
| to (Accrued Hours). Once Accrued Hours have equal or exceeded 24 that
| person will be rotated to the bottom of the list. If Smith accepts a
| 14 hour overtime he does not move because his Accured hours will then
| be 14. If Johnson accepts 14 hours he will move to the bottom of the
| list and then his Accured hours will be rest to zero.
|
| I know I am asking an impossible question. If anyone can help I will
| really appreciate the assistance. Thanks for your time.
|
| Mike Tonner
| Firefighter
| Sparks NV
|
|
| ---
| Message posted
|

 

[Otto Moehrbach]

Ken
If I'm reading the OP correctly, he doesn't want the relative position
of a firefighter to change (in the list) until that firefighter accumulates
24+ hours. Otto

 

[Otto Moehrbach]

Mike
What you want is not impossible at all. It is really rather simple but
it will take VBA. You didn't mention if all 3 lists would be in one file
but you can do so because only one piece of code (VBA) will be needed and it
will apply to whatever sheet (list) is being worked on at the time. I'll
make up a small file with the code in it and email it to you at your
"-nospam" address. HTH Otto

 

[ryanb.]

Otto,

if you would not mind, would you either email me the code or post it... I am
curious as to how it would be done.

thanks!!

ryanb.

Mike
What you want is not impossible at all. It is really rather simple but
it will take VBA. You didn't mention if all 3 lists would be in one file
but you can do so because only one piece of code (VBA) will be needed and it
will apply to whatever sheet (list) is being worked on at the time. I'll
make up a small file with the code in it and email it to you at your
"-nospam" address. HTH Otto

 

[Otto Moehrbach]

Ryan
I sent it to you. If you don't get it very soon, email me and give a
better address. I removed NO!!! and SPAM from yours. Remove cobia97 from
mine. HTH Otto

Otto,

if you would not mind, would you either email me the code or post it... I am
curious as to how it would be done.

thanks!!

ryanb.

Mike
What you want is not impossible at all. It is really rather simple but
it will take VBA. You didn't mention if all 3 lists would be in one file
but you can do so because only one piece of code (VBA) will be needed

and

 

[Otto Moehrbach]

Here is what I came up with along with an explanation. I welcome any
critique from anyone about a better way. HTH Otto
Here is some code that does what you want. I think. Play with
it in this file until you get the hang of it and tell me if this works for
you.
This code utilizes an event macro to trigger the rest of the
code. An event macro is simply a macro that executes automatically upon the
occurrence of some event. In this case I used the Worksheet_Change event.
This event macro is triggered if the content of any cell in the entire sheet
is changed. I added code to this event macro that says that if the target
cell (the cell that was changed to trigger the event macro) is not in Column
C
and not after Row 1, to do nothing.

I also added code to check if the entry made in Column C is a
number. If it isn't, a message box pops up and tells you so, and the
original number is reinstated in that cell. If everything is OK, the event
macro calls the macro that does the data changing and moving.

Like you stated you wanted, the entry made in Column C is added
to that in Column B. If this new number in Column B is less than 24 then
nothing happens beyond that. If it is 24 or greater then that person's name
is moved to the bottom of the list and the associated hour cells are blank.
The original row that had that person's name is then deleted.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 And Target.Row > 1 Then
If Target = "" Then Exit Sub
If Target = 0 Then Exit Sub
If Not IsNumeric(Target) Then
MsgBox "The entry is not a valid number. Try again."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
Call CalcData(Range(Target.Address), Target.Value)
End If
End Sub

Sub CalcData(i As Range, Hrs As Single)
Application.EnableEvents = False
i.Offset(, -1) = i.Offset(, -1) + i
If i.Offset(, -1) < 24 Then Exit Sub
i.Offset(, -2).Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
i.EntireRow.Delete
Application.EnableEvents = True
End Sub

Otto,

if you would not mind, would you either email me the code or post it... I am
curious as to how it would be done.

thanks!!

ryanb.

Mike
What you want is not impossible at all. It is really rather simple but
it will take VBA. You didn't mention if all 3 lists would be in one file
but you can do so because only one piece of code (VBA) will be needed

and

 

[Mikeyfyr]

Otto,

Thanks for helping. Can you please send the file again, my email
address was missing the ".us" suffix so I never got the file. When I
get it I'll try it out and let you know how it works.

Thanks again
Mike

 

[ryanb.]

Thanks Otto!

ryanb.

 

[Otto Moehrbach]

Mike
I sent it again but I have no confidence that I have the address right.
Email me direct and give me a good address for you. Remove "cobia97" from
my email address. Otto

 

[Otto Moehrbach]

Mike
The system would not take the address I have for you. I have
(e-mail address removed) without the dash and the nospam. That
is an Excel Forum address and is not your personal or business address.
Send me your address direct via email. Remove "cobia97" from my address.
Otto

 

[Mikeyfyr]

Otto,

I just wanted to thank you for taking the time to try and help me. I
have, however, found a solution in Access. Through a board similar to
this one and an I.T. guy with the city we have been able to solve most
of the problems. These type of boards have proven invaluable to me, I
wish I had found them sooner.

Thank you very much.

FWIW, my e-mail is Mikeyfyr, not Mikeeyfyr.

Mike