Creat a spreedsheet with moving rows?

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 from http://www.ExcelForum.com/

0
12/23/2003 8:27:13 PM
excel 39879 articles. 2 followers. Follow

8 Replies
616 Views

Similar Articles

[PageSpeed] 38

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

"Mikeyfyr" <Mikeyfyr.ywpzy@excelforum-nospam.com> wrote in message
news:Mikeyfyr.ywpzy@excelforum-nospam.com...
| 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 from http://www.ExcelForum.com/
|


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.555 / Virus Database: 347 - Release Date: 23/12/2003


0
rusty1 (18)
12/23/2003 8:45:57 PM
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
"Ken Russell" <rusty@theseams.com.au> wrote in message
news:3fe8a985$0$18690$afc38c87@news.optusnet.com.au...
> 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
>
> "Mikeyfyr" <Mikeyfyr.ywpzy@excelforum-nospam.com> wrote in message
> news:Mikeyfyr.ywpzy@excelforum-nospam.com...
> | 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 from http://www.ExcelForum.com/
> |
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.555 / Virus Database: 347 - Release Date: 23/12/2003
>
>


0
12/23/2003 9:14:35 PM
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
"Mikeyfyr" <Mikeyfyr.ywpzy@excelforum-nospam.com> wrote in message
news:Mikeyfyr.ywpzy@excelforum-nospam.com...
> 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 from http://www.ExcelForum.com/
>


0
12/23/2003 9:28:13 PM
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.

"Otto Moehrbach" <ottomocobia97@bellsouth.net> wrote in message
news:OpOAcvZyDHA.3216@TK2MSFTNGP11.phx.gbl...
> 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
> "Mikeyfyr" <Mikeyfyr.ywpzy@excelforum-nospam.com> wrote in message
> news:Mikeyfyr.ywpzy@excelforum-nospam.com...
> > 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 from http://www.ExcelForum.com/
> >
>
>


0
ryanb
12/23/2003 9:42:39 PM
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
"ryanb." <rblazeiN0!!!@SPAMidimn.com> wrote in message
news:OhTMx2ZyDHA.2328@TK2MSFTNGP10.phx.gbl...
> 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.
>
> "Otto Moehrbach" <ottomocobia97@bellsouth.net> wrote in message
> news:OpOAcvZyDHA.3216@TK2MSFTNGP11.phx.gbl...
> > 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
> > "Mikeyfyr" <Mikeyfyr.ywpzy@excelforum-nospam.com> wrote in message
> > news:Mikeyfyr.ywpzy@excelforum-nospam.com...
> > > 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 from http://www.ExcelForum.com/
> > >
> >
> >
>
>


0
12/23/2003 10:54:24 PM
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


"ryanb." <rblazeiN0!!!@SPAMidimn.com> wrote in message
news:OhTMx2ZyDHA.2328@TK2MSFTNGP10.phx.gbl...
> 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.
>
> "Otto Moehrbach" <ottomocobia97@bellsouth.net> wrote in message
> news:OpOAcvZyDHA.3216@TK2MSFTNGP11.phx.gbl...
> > 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
> > "Mikeyfyr" <Mikeyfyr.ywpzy@excelforum-nospam.com> wrote in message
> > news:Mikeyfyr.ywpzy@excelforum-nospam.com...
> > > 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 from http://www.ExcelForum.com/
> > >
> >
> >
>
>


0
12/23/2003 10:59:27 PM
Thanks Otto!

ryanb.

"Otto Moehrbach" <ottomocobia97@bellsouth.net> wrote in message
news:O$VkxhayDHA.3468@TK2MSFTNGP11.phx.gbl...
> 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


0
ryanb
12/28/2003 8:58:01 PM
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:D


---
Message posted from http://www.ExcelForum.com/

0
1/4/2004 11:22:33 PM
Reply:

Similar Artilces:

Creating PivotTable in Excel2007
How do you create a Pivot Table in Compatibility Mode in Excel 2007. The PivotTable need to be refreshed in Excel 2003. ...

Moving Large emails to Public folders
One of our clients has a Mac which is synchronized to his exchange account, they are running exchange 2003 sp2. When he attempts to move emails with slightly bigger attachments (e.g. 1.6mb) then it throws up this error: "The action cannot be completed due to an error on the exchange server". Entourage is fully up to date. Anything I can do? Cheers ...

SBS Exchange 2003: Create new user in AD but no exchange mailbox #3
I'm using SBS 2003. Exchange server is SP2. When I create a new user using Active Directory, it prompts me about creating a mail box. It says it's creating a mailbox, then it's done. But no mailbox appears. In fact, if I use the Exchange tasks wizard, I can create, delete, and recreate the mailbox--all say successful without an error message. And yet, no mailbox. This is even after I reboot the PC and sent email to the address. Any ideas what's going on? mike.aes@gmail.com wrote: > I'm using SBS 2003. Exchange server is SP2. > > When I create a new user ...

deleting multiple rows
Hello: I need to be able to delete multiple rows out of a worksheet. What I need to be able to do is have a function/macro that can identify a string of characters and then delete all the rows that have those characters. For example, if row 4, 18, and 45 all had "blue" in them (assume column is A), then I need something to go delete row, 4, 18, and 45. At the top of my worksheet I will have several, probably about 3, criteria, such as, "blue", "red", and "green" that will all need to be deleted. Now here's where I really need help. A row may ...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

OL2007+Exchange: Sent items disappear if i move them to another folder...!
I'm seeing a very strange issue with my Outlook2007 (against Exchange)... Sent items disappear if i move them to another folder...! Steps: 1. Send an email. Sent email gets saved under Outlook's default "Mailbox\SentItems" folder. 2. Go to that SentItems folder, and move the mail to another folder (let's say "Foobar") under your inbox. 3. Go to "Foobar", make sure mail is still there. 4. Let Outlook Sync up with Exchange server (or hit "Send and Receive"). 5. You will notice that the mail disappears... ! I did a full text search in my ...

How do you replace old data with new data without creating a new .
I have existing pivot tables and I want to replace the data source worksheet with new data and the pivot tables update with the new information. I have replace the data source but the pivot tables didn't update. Hit the "Refresh Data" in the pivot table toolbar (looks like an exclamation point) -- Regards, Dave "Dena" wrote: > I have existing pivot tables and I want to replace the data source worksheet > with new data and the pivot tables update with the new information. I have > replace the data source but the pivot tables didn't update. So...

Could Microsoft create a MSPOS user manual?
My customers are asking for one. Guess we were spoiled by RMS Store Operations having one available after Microsoft updated the SMS Commerce manuals. Makes me expect one for MSPOS even though most software no longer has printed manuals. -- Jeff Faul Merchants Solutions ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and t...

Viewing xml created in .Net in a ASP form VB6
I have been trying to create an xml document in VB.Net for displaying on an existing page written in ASP. im having a type mismatch error in my code. When i view my XML i have the following included in the <binary> node. <Binary xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">DATA HERE </Binary My issue is how do i set the datatype of the the node using dotnet so that i can replicate the same node structure and use my existing ASP page. thanks in advance Colin Graham ...

Moving Folders?
How do I move folders from one account to another? I want to move all my folders from my hotmail account to my Comcast account. How do I move folders from one account to another? Please disregard this post. Posted twice mistakenly... Please respond to the person that replied to your original post about this issue. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Artreid" <artreid44@hotmail.com> wrote in message news:uKkBQpxFLHA.1316@TK2MSFTNGP02.phx.gbl... > How do I move folders from one account to another? I want t...

Move Data From Vertical to Horizontal
How do I get my speadsheet to move from vertical meaning: Name Type Hours Bob Regular 80.00 Bob OT 10.00 To look like this: Name Regular OT Bob 80.00 10.00 If anyone can help PLEASE comment. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You can create a pivot table to summarize the data. There are some instructions and links here: http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm For ...

Creating a form or template
We scanned in an image of a post it note that has graphics on it. It is a jpg file. Can we somehow make this jpg file a form or template in Outlook? Ideally, we want to pull up a form that looks like the image of a post it note with the graphics in tact and be able to compose a message on the email post it note. How do you accomplish this? Thanks! ...

Error after mailbox move E2K
Hi! Yesterday I moved a mailbox from one E2K server to the other one. Event log at server 1 told me that it was finished sucessfully. It disappeared from the Exchange System Manager view at server 1.Server 2 shows a red cross in front of the mailbox. In the context menu I am offered to reconnect the mailbox. But after choosing this I'm told that it is already connected to a user, that replication is not finished. But this ongoing for too many hours. Server 1 shows event 1205 in the event log (mailbox of this user cannot be created due to error 0x50x). I also cannot move the mailbox back o...

Combine rows to print on one label
I have imported student information into an excel spreadsheet (use Excel 2003). Each student has one row of information for each course they take. I need to print one label for each student and each course & grade must print on a single label. How do I combine the rows to cause this to happen? assuming the info in the row is divided into separate columns for name, course, grade, etc you can do a mail merge in word using the spreadsheet as the data source "Clearcreek" wrote: > I have imported student information into an excel spreadsheet (use Excel > 2...

Trying to move messages to folders
When I try to move certain email messages to other folders (manually), suddenly I get this message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." Even with the restarts, I still get the error messages. I looked up the error message in the database, but it seems to be listed only in cases where you're forwarding mail, not moving it. I also checked for updates to Outook 2002, but the scan said I was up-to-date. This all happened, by the way, after I installed Service Pack 2. Can anyone help? Thanks so much! ...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

How do i keep the colors applied to the specific rows when sortin.
I have applied specific colors to specifc rows, but when i sort the colored rows follow the sorting format. ...

deleting duplicate rows
I have an import file that might have duplicates that I don't want to deal with. I'm creating a temp table from the import file and want to delete the duplicates from the temp table before proceding with other code. Here's an example table: create table #temp (itemnum int, firstname varchar(10), notes varchar(50)) insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some note') insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some other note') insert into #temp (itemnum, firstname, notes) values (2, ...

Insufficient Permissions to Create Tasks
Hi, Exchange 5.5, Windows 2000, Outlook 2000. I have a user who is trying to create tasks and he gets a message that says that he does not have permission to do this. He can edit tasks and delete tasks, but not create them. We've run the repair utility, reinstalled, thought that it had to do with the PALM software and removed that, and there has been no change. Any help would be much appreciated! Thanks! ...

Fonts do not print in colur used when document created.
When I create a document in either Word ot Publisher the text is not always printed in the colour selected. This problem seems particular to Red & Blue. can any one help please. Peeter Have you tried some maintenance on your printer? Are you saving as a PDF? This problem has been reported using Microsoft's save as add-in. -- Mary Sauer http://msauer.mvps.org/ "Peter Piper" <PeterPiper@discussions.microsoft.com> wrote in message news:76737C66-2B07-42DF-BFC5-828A14CE135F@microsoft.com... > When I create a document in either Word ot Publisher t...

Moving Exchange DB's to a new server (different name)
In a DR test, we moved SG's to a new server, different server name, same SG and MS name. MS mounts, can not connect to mailbox. Delete mailbox, create a new one, we can then connect. Delete this mailbox, reconnect to the original, and now we can connect. How can we accomplish this in bulk? In other words, after doing a system state restore of AD, then a system state of Exchange, restored MS file, mounted, etc. We were unable to connect to these mailboxes. I am assuming this is related to GUID's. Any thoughts? Users in AD specify a server by name. When you say that you move mailbo...

Resize rows in a protected sheet
Hi there. I'm sending out a protected worksheet with some cells that the users can enter variable amounts of text. I've set the size so they can comfortably fit in about 50 words but I'd like them to have the flexibility to resize the row height when they're editing the worksheet. But because it's protected, they can't. Is there anyway I can allow them to change the row height dynamically? A button or something?? Andrew A workaround. Format the alignment in the cell to Wrap text. Users can Keep entering text or returns (ALT+ENTER) in the cell and it will aut...

Creat new dictionery
It says when I try to add a new word to my dictionery that it is full. How do I expand same, or create a new dictionery? I use WORD 2003 See http://sbarnhill.mvps.org/WordFAQs/MasterSpellCheck.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "kernaghanl" <kernaghanl@discussions.microsoft.com> wrote in message news:BBC3C828-16EF-4861-B3A5-2A7CC406F43C@microsoft.com... > It says when I try to add a new word to my dictionery that it is full. > How > do I expand same, or create a new dictione...

Choosing FROM when creating an e-mail
I have several e-mail accounts. When creating a new e- mail message sometimes I like the option of choosing a different e-mail account the new e-mail is 'from' rather than the default account. This function is possible in Outlook Express via a drop down list in the 'FROM' field when creating an e-mail, but Outlook doesn't even have a 'FROM' field and uses the default e-mail address as the return address. Please advise if this is possible in Outlook. Thanks. (PS I'm using Outlook 2000) Outlook 2000 Corporate or Workgroup mode (check Help->About) doe...

Autofit rows
I am having an issue with autofitting rows in a spreadsheet of imported text in Excel 2003. There are no merged cells in the document, and none of the cells go over the 1024 character limit. When I select all rows and attempt to autofit the rows to the contents of the cells, some of the rows resize properly, and some leave one or several lines of white space below the bottom of the text in the longest cell. I can resize them manually, but this is data that is imported daily and runs to several thousand rows. If I widen the column where I am having the problem, once it reaches a certain ...