Historical Data - Progress mesurement

[Guest]

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

 

[vezerid]

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

 

[Guest]

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)
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]

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

 

[Guest]

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

 

[vezerid]

Steen,

email me your workbook if you want. It is probably easier to just build
a VBA solution on it than try to describe solutions via the group.

vezerid at act dot edu

Put whatever comments you deem necessary.

Regards,
Kostis

 

[Guest]

Hi again

That's just the kind of help one could never expect - great.

I will sent the excell sheet later this evening (Danish Time ).

I the mean time I have been working on another solution where the formulas
up to ToDay - 1 is convertet to Value (macro doing copy and then paste until
today - 1). This seems to be a solution - the issue is ofcouse the trigger
time. I am trying to make it run every time the sheet is opend.

Example:
-------------
Option Explicit
Sub Auto_ChgFormelToValue()

Dim DateCol As Range
Dim Res As Variant
Dim B_ToDay As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("Graf Data").Select
Set DateCol = Range("A5").EntireColumn

B_ToDay = CLng(Date - 1)

Res = Application.Match(B_ToDay, DateCol, 0)

If IsError(Res) Then
MsgBox "Today weren't found!"
Else
DateCol.Cells(1)(Res).Select

Range("F3:N" & Res).Select
Selection.Copy
Range("F3:N" & Res).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
Sheets("IssueLog").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
--------------
The columns and worksheet with information is a littlel different than the
examples below. It's not a nice solution, but it seems to be working, besides
the auto_ start feature. Still working on it.

If you have another solution (and better) I would be glad to hear from you
when you have taken a look at the worksheet.

/Steen