How to track over data that is over written every time the file is used.

[DMPMO]

This post is really to ask for a good suggestion on how to accomplish this task.

I have made a Template in excel that calculates sales opportunities. The first few columns data is directly imported from the amount of new business sales. The last few columns is where the formulas are stored and simply does % of things like how much was sold and how much sales opportunity exists within the new business. Since the data is imported every time it is used those percentages change over time.


What I am attempting is to find a way to make a independent column where those percentages is tracked over time that is automatically updated when the data is imported. I am aware this can be done by manually entering the date and percentage every time. Can this be done without using VBA?

 

[mrmmickle1]

You can use a worksheet change event to accomplish your task. For example when a user pastes data into the file it would trigger a macro that does X task (The action that you describe above). Here is some sample code (Needs to be pasted in a worksheet module):

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_Change([COLOR=#0000ff]ByVal[/COLOR] Target [COLOR=#0000ff]As[/COLOR] Range)
    MsgBox "I just changed something on the worksheet"
[COLOR=#0000ff]End Sub[/COLOR]