Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Capturing Trend Data over time
Consider the following data set:
A2:B4 = Apples 25 Bread 04 Milk 12 where the values (B2:B4) change on a daily and random basis, but the series (A2:A4) remain the same. Then I have another range of cells, D1:I4 the purpose of which is to hold the historical values of the different series mentioned in A2:A4, on specific dates= 05 Jan 09 12 Jan 09 19 Jan 09 26 Jan 09 02 Feb 09 Apples 14 22 12 16 25 Bread 02 03 05 01 04 Milk 10 09 07 11 12 I need a formulae that will look at the value of each series in B2:B4, an if TODAY is = to the date in E1:I1, the value of that series will be transferred into the relvant cell and frozen there for trend analysis purposes. E.g. If today's date is 12 Jan 09, then whatever the values are for Apples, Bread and Milk as contained in B2:B4, will automatically be transferred and stored in F2:F4. Once the value is stored it will not change further and becomes a historic "snapshot" of the static values for that day. Then the same process occurs for the next week when TODAY =19 Jan 09, the values contained in B2:B4 will again be stored in G2:G4 and so on. I have treid working with = between TODAY and the given target date, but it does not "store" the data, and keeps dynamically updating the entire set of "hisotric" data to the current values of the dynamic set in B2:B4. Please hep and thanks in advance! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Capturing Trend Data over time
There's no way to use formulas to achieve what you're after. Try posting
afresh in excel.programming for a macro/vba solution -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "WildWill" wrote: Consider the following data set: A2:B4 = Apples 25 Bread 04 Milk 12 where the values (B2:B4) change on a daily and random basis, but the series (A2:A4) remain the same. Then I have another range of cells, D1:I4 the purpose of which is to hold the historical values of the different series mentioned in A2:A4, on specific dates= 05 Jan 09 12 Jan 09 19 Jan 09 26 Jan 09 02 Feb 09 Apples 14 22 12 16 25 Bread 02 03 05 01 04 Milk 10 09 07 11 12 I need a formulae that will look at the value of each series in B2:B4, an if TODAY is = to the date in E1:I1, the value of that series will be transferred into the relvant cell and frozen there for trend analysis purposes. E.g. If today's date is 12 Jan 09, then whatever the values are for Apples, Bread and Milk as contained in B2:B4, will automatically be transferred and stored in F2:F4. Once the value is stored it will not change further and becomes a historic "snapshot" of the static values for that day. Then the same process occurs for the next week when TODAY =19 Jan 09, the values contained in B2:B4 will again be stored in G2:G4 and so on. I have treid working with = between TODAY and the given target date, but it does not "store" the data, and keeps dynamically updating the entire set of "hisotric" data to the current values of the dynamic set in B2:B4. Please hep and thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Code Required for Capturing Time | Excel Discussion (Misc queries) | |||
capturing all of the data | Excel Worksheet Functions | |||
Capturing average turn around time with constraints | Excel Worksheet Functions | |||
Date Capturing by Time | Excel Discussion (Misc queries) | |||
Capturing Rows of Data | Excel Discussion (Misc queries) |