Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Data - Progress mesurement
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Data - Progress mesurement
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Data - Progress mesurement
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Data - Progress mesurement
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Data - Progress mesurement
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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Data - Progress mesurement
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 Steen wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Historical Data - Progress mesurement
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 "vezerid" wrote: 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 Steen wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
Web query to create historical stock data | New Users to Excel | |||
ranking query | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) |