Historical Data - Progress Mesurement - Track changes - Log
 

Hi All

Any help on the below problem would be much appriciated.

/Steen
-------------------
Hi again

Thanks again for your quick answer - it seen like I am in a dead end :-(

You are right - I want to have a log/track of changes in the status (and
other issuses) on a daily basis. Do you have any idee of where to put this
question to have a chance of some help?

/Steen
-----------------------------

Steen,
If I understand correctly, you want a log, to track the changes. Do you
want whenever you go to an entry in Task List and change the status,
i.e. *overwrite the cell*, the history to be updated? This would
likely require VBA.

The way you have your layout it is easier to go from history to
tasklist than the other way around.

You could consider changes in your task list, every entry in which can
be the ID, the date and the new status. This could make it easier to
generate a history list.

Write back if you are considering a change in your input table
structure/functionality. Or maybe someone else jumps in.

Kostis

Steen wrote:
Hi Kostis

Thanks for your answer. I have tryed to fool a little around with your
suggestion, but it seems to have the same problem as my formula - just a
little smarter :-)

The problem is still that all "old" data is change when changing the status.
It's a bit dificult to explain and I have therefor added a GRAPH DATA of how
I want it to be

STEP TWO (STEP ONE see below)
SHEET ONE - TASKLIST
A B C
ID STATUS STATUS CH Comment
-------------------------------------------------------------
1 Working 2006-11-01 New-Working; 2006-10-28-2006-11-01
2 Working 2006-10-29
3 Working 2006-11-01

SHEET TWO - GRAPH DATA
A B C D
Date New Working Closed Comment
2006-10-28 0 0 0 New: 1-0 Wanted: Still 1
2006-10-29 0 1 0 New: 1-0 Wanted: Still 1
2006-10-30 0 1 0 New: 1-0 Wanted: Still 1
2006-11-01 0 3 0 New: 1-0 Ok;Working: 2-3 OK
2006-11-02 #NA #NA #NA ToDay = 2006-11-01


The problem can be drilled down on how to "overwrite" the formula with the
value of the result from the formula ---- I think :-) Can you try to help
again?


"vezerid" wrote:

Steen,

in B2 of your target sheet you can use the following formula and copy
down and across as necessary:

=SUMPRODUCT((TaskList!$B$5:$B$317=B$1)*(TaskList!$ C$5:$C$317=$A2))

HTH
Kostis Vezerides

Steen wrote:
Hi

I would like some help on how to make historical data - progress mesurement
in the following case:

SHEET ONE - TASKLIST
A B C
ID STATUS STATUS CHANGED
------------------------------------------------
1 New 2006-10-28
2 Working 2006-10-29
3 Working 2006-11-01

SHEET TWO - GRAPH DATA
A B C D
Date New Working Closed
2006-10-28 1 0 0
2006-10-29 1 1 0
2006-10-30 1 1 0
2006-11-01 1 2 0
2006-11-02 #NA #NA #NA ToDay = 2006-11-01

I am using a formula like the one below today:
=IF('Graf Data'!$A3<=TODAY();IF(TaskList!$C$5:$C$317<='Graf
Data'!$A3;SUM(IF(IssueLog!$B$5:$B$317='Graf Data'!B$1;1;0);0);0);NA())

This formula almost does the task, but it has a problem when for example New
above is changed to Working (data i automatical set to current date) all old
(historical) data for col B in GrafData is set to 0 - old data is
overwritten. The Working Col. is updated from 2 to 3 as wanted with hist.
data intact.

Any help on this problem would be much appriciated