Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Can a macro erase it's self after a set criteria?
I have a spread sheet that I use that has a macro that extracts data from a DB.
This is great for a snap shot but I want to capture this data over time. Is it possible to set a formula/macro in a cell that would do 1 of three things 1) tomorrow €“ do nothing, maintain the formula in the cell 2) today €“ copy data from a different location or run a formula 3) Yesterday €“ force the data captured yesterday to stay in the cell as a value. I hope that makes sense. -- Jeff |
#2
|
|||
|
|||
On Wed, 12 Oct 2005 13:40:02 -0700, "Jeff"
wrote: I have a spread sheet that I use that has a macro that extracts data from a DB. This is great for a snap shot but I want to capture this data over time. Is it possible to set a formula/macro in a cell that would do 1 of three things 1) tomorrow – do nothing, maintain the formula in the cell 2) today – copy data from a different location or run a formula 3) Yesterday – force the data captured yesterday to stay in the cell as a value. I hope that makes sense. I'd be inclined to have three objects or controls on the s/s, each one linked to its own macro and which would run one of your options above. If you want further advice please post back with a few more details. e.g. what's the formula in the cell (1), what's the cell reference of the different loacation (2) and what does it contain? HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
If you want more info I will try to supply,
Basically, I dump a DB into Excel and on a summary page put together a snap shot of current status on the project. The cells on the summary page contain formulas that derive a number from the DB. Several countif, dsum, and aray formulas. This works great for looking at the data at a monment in time But I would like to start tracking this over time and generating a trend line. sure I can go into the sheet and copy and past the values to a time based table on a different page but I would like to set the page up in the work book and update it's self via a formula or function. 1) I would like the cells relating to any day in the future to be blank 2) I would like the cells relating to today to update themselves from the summary sheet as often as I update the sheet. 3) I would like dates in the past to maintian the last value they loaded. Does that help? -- Jeff "Richard Buttrey" wrote: On Wed, 12 Oct 2005 13:40:02 -0700, "Jeff" wrote: I have a spread sheet that I use that has a macro that extracts data from a DB. This is great for a snap shot but I want to capture this data over time. Is it possible to set a formula/macro in a cell that would do 1 of three things 1) tomorrow €“ do nothing, maintain the formula in the cell 2) today €“ copy data from a different location or run a formula 3) Yesterday €“ force the data captured yesterday to stay in the cell as a value. I hope that makes sense. I'd be inclined to have three objects or controls on the s/s, each one linked to its own macro and which would run one of your options above. If you want further advice please post back with a few more details. e.g. what's the formula in the cell (1), what's the cell reference of the different loacation (2) and what does it contain? HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
|
|||
|
|||
I'm interpreting this to mean that the same cell will contain
different values at any time. In which case I suggest that VBA macros attached to command buttons or something similar, and the worksheets change event are what you want, rather than Excel functions. That said, it would appear that 1 & 2 below could be dynamic functions using lookups or array functions with conditions, since for 1 you can generate future dates based on today's date. And today's date can be derived from the =Now() function. You probably still need a macro for item 3 since it appears you want to Copy and then PasteSpecial(xlvalues), and this macro needs to understand what is meant by past dates i.e. dates earlier than the =Now() function. HTH On Wed, 12 Oct 2005 18:04:07 -0700, "Jeff" wrote: If you want more info I will try to supply, Basically, I dump a DB into Excel and on a summary page put together a snap shot of current status on the project. The cells on the summary page contain formulas that derive a number from the DB. Several countif, dsum, and aray formulas. This works great for looking at the data at a monment in time But I would like to start tracking this over time and generating a trend line. sure I can go into the sheet and copy and past the values to a time based table on a different page but I would like to set the page up in the work book and update it's self via a formula or function. 1) I would like the cells relating to any day in the future to be blank 2) I would like the cells relating to today to update themselves from the summary sheet as often as I update the sheet. 3) I would like dates in the past to maintian the last value they loaded. Does that help? __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
Extract specific data into its own workbook via macro? | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |