Count How Many Times A Cell's Value Has Been Changed

Hi,

I run a manufacturing production sheet in Excel 2002 to plan
production.

I have approx 500 rows of orders. I have columns labelled Mon, Tue,
Wed, Thu, Fri. Each day I sit with my supervisors and try to agree
which orders we will complete and on which day. Once agreed I will
place the order £'s value along the row under a day the supervisor has
promised, eg.

Mon Tue Wed Thu Fri

Order1-Hammer £2
Order2-Wrench £5
Order3-Screw £5
etc..etc.

The problem I have is that as each day goes by, my supervisors 'delay'
their promises and I have to move their commited date forward by a
day. eg.


Mon Tue Wed Thu Fri

Order1-Hammer £2
Order2-Wrench £5
Order3-Screw £5
etc..etc.

What I need excel to do is 'COUNT' how many times the supervisor broke
his promise and I had to change the production date. In the example
above all 3 orders were changed once each.

I need excel to somehow remember the the change!

I appreciate your help in advance because I know this is going to be a
real nightmare of a job!!

Thanks,

Avais

Avias
A number of methods for doing what you want come to mind, but not
knowing your operation and/or data layout, it's hard to give you anything
definitive.
One idea is this:
Say you have one column for order number, another column for part name, and
five columns for the days of the week. That's 7 columns. Let's say that
you color the eighth column, Column H, some color so that Column H stands
out. When you enter something in any of the five day columns, Excel does
nothing. But if you click on Column H (in the row in question) Excel will
pop up a query and ask you how many days to shift the value in that row.
When you enter a number in response, Excel will shift the value that many
days. At the same time, Excel will make a record of the shift on another
sheet, maybe including the date of the shift, and total up anything you
want.
There are other ways of having Excel "remember" the number of shifts.
But anyway you choose to go, Excel will need to know, somehow, that an entry
is an initial entry and not a shift of an already existing entry.
Post back if this interests you. Also include more detail about your
data layout. For instance, does your data really have only 5 days? HTH
Otto

"Avais" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I run a manufacturing production sheet in Excel 2002 to plan
> production.
>
> I have approx 500 rows of orders. I have columns labelled Mon, Tue,
> Wed, Thu, Fri. Each day I sit with my supervisors and try to agree
> which orders we will complete and on which day. Once agreed I will
> place the order £'s value along the row under a day the supervisor has
> promised, eg.
>
> Mon Tue Wed Thu Fri
>
> Order1-Hammer £2
> Order2-Wrench £5
> Order3-Screw £5
> etc..etc.
>
> The problem I have is that as each day goes by, my supervisors 'delay'
> their promises and I have to move their commited date forward by a
> day. eg.
>
>
> Mon Tue Wed Thu Fri
>
> Order1-Hammer £2
> Order2-Wrench £5
> Order3-Screw £5
> etc..etc.
>
> What I need excel to do is 'COUNT' how many times the supervisor broke
> his promise and I had to change the production date. In the example
> above all 3 orders were changed once each.
>
> I need excel to somehow remember the the change!
>
> I appreciate your help in advance because I know this is going to be a
> real nightmare of a job!!
>
> Thanks,
>
> Avais

Hi Avais

this is not easy to do in Excel .. basically the only ways i can think of
are:
1. rather than deleting the first promised date and placing the value in the
second, leave it in the first place and just change the font to light grey
or whatever, then you can see what is the currently promised date and also
the earlier promises
2. everytime you change the schedule take a copy of it first and date & time
it ....then you have a history as to the progress of the production
3. use project management software - not sure if any excel gantt chart
add-ins allow you to "baseline" the data, but you might like to investigate
the use of Project 2002 / 2003 where you can keep 11 baselines & 11 interim
plans (copies) of your project and compare your current plan against these
baselines/interims... however, there are other things to take into
consideration before i would say that a project schduling tool like MS
Project would be your best option - if you're interested in knowing more
about this, please post back.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Avais" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I run a manufacturing production sheet in Excel 2002 to plan
> production.
>
> I have approx 500 rows of orders. I have columns labelled Mon, Tue,
> Wed, Thu, Fri. Each day I sit with my supervisors and try to agree
> which orders we will complete and on which day. Once agreed I will
> place the order £'s value along the row under a day the supervisor has
> promised, eg.
>
> Mon Tue Wed Thu Fri
>
> Order1-Hammer £2
> Order2-Wrench £5
> Order3-Screw £5
> etc..etc.
>
> The problem I have is that as each day goes by, my supervisors 'delay'
> their promises and I have to move their commited date forward by a
> day. eg.
>
>
> Mon Tue Wed Thu Fri
>
> Order1-Hammer £2
> Order2-Wrench £5
> Order3-Screw £5
> etc..etc.
>
> What I need excel to do is 'COUNT' how many times the supervisor broke
> his promise and I had to change the production date. In the example
> above all 3 orders were changed once each.
>
> I need excel to somehow remember the the change!
>
> I appreciate your help in advance because I know this is going to be a
> real nightmare of a job!!
>
> Thanks,
>
> Avais

How about event code with hidden worksheet with mirror entries for each
entry that gets updated every time the entry shifts along a day? You could
do counts then by number of days shunted!

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Avais
>
> this is not easy to do in Excel .. basically the only ways i can think of
> are:
> 1. rather than deleting the first promised date and placing the value in
the
> second, leave it in the first place and just change the font to light grey
> or whatever, then you can see what is the currently promised date and also
> the earlier promises
> 2. everytime you change the schedule take a copy of it first and date &
time
> it ....then you have a history as to the progress of the production
> 3. use project management software - not sure if any excel gantt chart
> add-ins allow you to "baseline" the data, but you might like to
investigate
> the use of Project 2002 / 2003 where you can keep 11 baselines & 11
interim
> plans (copies) of your project and compare your current plan against these
> baselines/interims... however, there are other things to take into
> consideration before i would say that a project schduling tool like MS
> Project would be your best option - if you're interested in knowing more
> about this, please post back.
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> "Avais" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I run a manufacturing production sheet in Excel 2002 to plan
> > production.
> >
> > I have approx 500 rows of orders. I have columns labelled Mon, Tue,
> > Wed, Thu, Fri. Each day I sit with my supervisors and try to agree
> > which orders we will complete and on which day. Once agreed I will
> > place the order £'s value along the row under a day the supervisor has
> > promised, eg.
> >
> > Mon Tue Wed Thu Fri
> >
> > Order1-Hammer £2
> > Order2-Wrench £5
> > Order3-Screw £5
> > etc..etc.
> >
> > The problem I have is that as each day goes by, my supervisors 'delay'
> > their promises and I have to move their commited date forward by a
> > day. eg.
> >
> >
> > Mon Tue Wed Thu Fri
> >
> > Order1-Hammer £2
> > Order2-Wrench £5
> > Order3-Screw £5
> > etc..etc.
> >
> > What I need excel to do is 'COUNT' how many times the supervisor broke
> > his promise and I had to change the production date. In the example
> > above all 3 orders were changed once each.
> >
> > I need excel to somehow remember the the change!
> >
> > I appreciate your help in advance because I know this is going to be a
> > real nightmare of a job!!
> >
> > Thanks,
> >
> > Avais
>
>

Hi Bob

so you mean rather than doing my option 1 manually ... have the computer
maintain this in a hidden sheet using worksheet change code ... i did think
of this, however couldn't visualise how to deal with things like the
original sheet getting sorted or rows entered etc .... any ideas if an
automatic solution could be used and these events catered for?

--
Cheers
JulieD

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> How about event code with hidden worksheet with mirror entries for each
> entry that gets updated every time the entry shifts along a day? You could
> do counts then by number of days shunted!
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JulieD" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Avais
>>
>> this is not easy to do in Excel .. basically the only ways i can think of
>> are:
>> 1. rather than deleting the first promised date and placing the value in
> the
>> second, leave it in the first place and just change the font to light
>> grey
>> or whatever, then you can see what is the currently promised date and
>> also
>> the earlier promises
>> 2. everytime you change the schedule take a copy of it first and date &
> time
>> it ....then you have a history as to the progress of the production
>> 3. use project management software - not sure if any excel gantt chart
>> add-ins allow you to "baseline" the data, but you might like to
> investigate
>> the use of Project 2002 / 2003 where you can keep 11 baselines & 11
> interim
>> plans (copies) of your project and compare your current plan against
>> these
>> baselines/interims... however, there are other things to take into
>> consideration before i would say that a project schduling tool like MS
>> Project would be your best option - if you're interested in knowing more
>> about this, please post back.
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ...well i'm working on it anyway
>> "Avais" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > I run a manufacturing production sheet in Excel 2002 to plan
>> > production.
>> >
>> > I have approx 500 rows of orders. I have columns labelled Mon, Tue,
>> > Wed, Thu, Fri. Each day I sit with my supervisors and try to agree
>> > which orders we will complete and on which day. Once agreed I will
>> > place the order £'s value along the row under a day the supervisor has
>> > promised, eg.
>> >
>> > Mon Tue Wed Thu Fri
>> >
>> > Order1-Hammer £2
>> > Order2-Wrench £5
>> > Order3-Screw £5
>> > etc..etc.
>> >
>> > The problem I have is that as each day goes by, my supervisors 'delay'
>> > their promises and I have to move their commited date forward by a
>> > day. eg.
>> >
>> >
>> > Mon Tue Wed Thu Fri
>> >
>> > Order1-Hammer £2
>> > Order2-Wrench £5
>> > Order3-Screw £5
>> > etc..etc.
>> >
>> > What I need excel to do is 'COUNT' how many times the supervisor broke
>> > his promise and I had to change the production date. In the example
>> > above all 3 orders were changed once each.
>> >
>> > I need excel to somehow remember the the change!
>> >
>> > I appreciate your help in advance because I know this is going to be a
>> > real nightmare of a job!!
>> >
>> > Thanks,
>> >
>> > Avais
>>
>>
>
>