Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count How Many Times A Cell's Value Has Been Changed
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" wrote in message m... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count How Many Times A Cell's Value Has Been Changed
Hi Avais
this is not easy to do in Excel .. basically the only ways i can think of a 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" wrote in message m... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count How Many Times A Cell's Value Has Been Changed
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" wrote in message ... Hi Avais this is not easy to do in Excel .. basically the only ways i can think of a 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" wrote in message m... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count How Many Times A Cell's Value Has Been Changed
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" wrote in message ... 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" wrote in message ... Hi Avais this is not easy to do in Excel .. basically the only ways i can think of a 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" wrote in message m... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count text appear how many times & put in respec col??eg 1st times | Excel Discussion (Misc queries) | |||
How to determine if a cell's data has been changed | Excel Discussion (Misc queries) | |||
I need to keep a count of the times a cell is changed | Excel Discussion (Misc queries) | |||
Trigger an event when a cell's value is changed. | Excel Programming | |||
Alter one cell value, in case another cell's value changed | Excel Programming |