Excel to store history information and display a count of reschedules

[CollyMitch]

Hi,

I currently copy data from a system into an excel spreadsheet on a
weekly basis, used to store a history of implementation records. For
each record I store a unique ID, target date, owner, description,
category, approval status and a result. Each week I create a new
worksheet/tab with the date for the name. Each implemenation will take
about 3 weeks and therefore the data will be repeated in about 3
worksheets, albeit with slight changes to approval status, target date
or descriptions.

What I want to do is to calculate, for each record (implementation),
the amount of times the target date is changed, over the period of time
(thereby recording how many times an implementation is delayed.)

Is there a method of 'linking' a unique ID to the same ID on the other
sheet and then using this to create a 'Delayed changes' worksheet. In
this I would like the following column headings eg Change ID, Owner,
Delay count.

If possible the following fields would be useful (Original Target date,
second, third, current etc.)

Anybody got an idea of how I could go about this?

Thanks,

Colm

 

[CollyMitch]

So it's impossible is it?

 

[Puppet_Sock]

So it's impossible is it?

What's impossible? You didn't quote any text, so it's pretty hard
to know what you are talking about.
Socks

 

[Puppet_Sock]

CollyMitch wrote:
[snip]

What I want to do is to calculate, for each record (implementation),
the amount of times the target date is changed, over the period of time
(thereby recording how many times an implementation is delayed.)

You've got kind of a vague outline of your requirements.

If I understand what you are asking, then you need to keep some
kind of history. Each time you change the value, you will need to
record:
- The time the value was changed.
- The old value.
- The new value.

Then, when you want a report, you will need to do some analysis
of this history. For example, if a value is changed back to an old
value, you will have to decide what that means to your report. Does
that count as a change? Does the replaced value still count as a
delay? What if the user indicates they are changing the value but
just type in the same old value? Or what if the date is changed from
a later date to an earlier date? Is that still a "delay?" And so on.

So, you would want to force changes of the date to go through some
interface. Maybe protect the sheet and put a button in that will pop
up a dialog that the user can enter the new value. Then the code
behind it will save the history, possibly on another sheet, doing all
the unprotecting/protecting as required.

Is there a method of 'linking' a unique ID to the same ID on the other
sheet and then using this to create a 'Delayed changes' worksheet. In
this I would like the following column headings eg Change ID, Owner,
Delay count.

If possible the following fields would be useful (Original Target date,
second, third, current etc.)

Anybody got an idea of how I could go about this?

There are ways to do this. I'd be writing quite a bit of VB code if it
were my task.

Think about things like a data-holder worksheet. Maybe the first
row contains things like:
- an item count in A1
- a count of histories for items in B1, C1, etc.

Then the histories could start in a particular row and following.
So some simple VB code would get you the location and size
of any given item's history.

You might then want to write some VB that would alllow adding
a new item, modiftying existing items, possibly deleting items
when there got to be too many.

Note that the kind of stuff you've got here is straying towards what a
database is more oriented towards. Linking up IDs, keeping track of
histories, that sort of thing, is certainly possible on a spreadsheet.
It's just that it is probably easier in a database. So if you have not
gone too far doing this in Excel, you might want to consider Access,
especially if you don't need too many of the spreadsheet type features.
Socks

 

[CollyMitch]

Thanks Socks,

Yeah I think database would be the way forward too. I'll work towards a
design for that as I know how to use databases. Having them in a
spreadsheet just meant records were duplicated each week which is a
waste.

Thanks for your help,

Colly.

CollyMitch wrote:
[snip]

What I want to do is to calculate, for each record (implementation),
the amount of times the target date is changed, over the period of time
(thereby recording how many times an implementation is delayed.)

You've got kind of a vague outline of your requirements.

If I understand what you are asking, then you need to keep some
kind of history. Each time you change the value, you will need to
record:
- The time the value was changed.
- The old value.
- The new value.

Then, when you want a report, you will need to do some analysis
of this history. For example, if a value is changed back to an old
value, you will have to decide what that means to your report. Does
that count as a change? Does the replaced value still count as a
delay? What if the user indicates they are changing the value but
just type in the same old value? Or what if the date is changed from
a later date to an earlier date? Is that still a "delay?" And so on.

So, you would want to force changes of the date to go through some
interface. Maybe protect the sheet and put a button in that will pop
up a dialog that the user can enter the new value. Then the code
behind it will save the history, possibly on another sheet, doing all
the unprotecting/protecting as required.

Is there a method of 'linking' a unique ID to the same ID on the other
sheet and then using this to create a 'Delayed changes' worksheet. In
this I would like the following column headings eg Change ID, Owner,
Delay count.

If possible the following fields would be useful (Original Target date,
second, third, current etc.)

Anybody got an idea of how I could go about this?

There are ways to do this. I'd be writing quite a bit of VB code if it
were my task.

Think about things like a data-holder worksheet. Maybe the first
row contains things like:
- an item count in A1
- a count of histories for items in B1, C1, etc.

Then the histories could start in a particular row and following.
So some simple VB code would get you the location and size
of any given item's history.

You might then want to write some VB that would alllow adding
a new item, modiftying existing items, possibly deleting items
when there got to be too many.

Note that the kind of stuff you've got here is straying towards what a
database is more oriented towards. Linking up IDs, keeping track of
histories, that sort of thing, is certainly possible on a spreadsheet.
It's just that it is probably easier in a database. So if you have not
gone too far doing this in Excel, you might want to consider Access,
especially if you don't need too many of the spreadsheet type features.
Socks