Can a macro erase it's self after a set criteria?

[Jeff]

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.

 

[Richard Buttrey]

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
__________________________

 

[Jeff]

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]

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

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
__________________________