preserve number in cell of excel

[punteray]

For my program I need the odds at different MTP. I am displaying LIVE ODDS.

I have a formula in a cell that says"when MTP=xx then cell yy equal the displayed odds. When MTP changes that number goes away. I need to preserve that number but in my research I have not been able to come up with a viable method.:mad:

Any suggestions??

Ray

[HUSKER55]

just a thought, it appears the cells is linked to the active cell which changes when the MTP changes

maybe, set another cell to that value

if(mtp=2,z4=yy4,0) or something along that line. that way the only time that cell would contain a value is when the mtp was 2 and there is nothing to change the value.

maybe.

good luck

[traveler]

Have you looked at the "Choose" formula.

[hcap]

You probably want to set up some code that is event driven that copies the value of that cell to another location so you have a permanent record. Not worked out completely, but try this. It should give you a notion of event driven code. In this case when a range changes.

Right click on the worksheet tab where you are working. View code
A vba window should open. Insert this code.
"vrange" should be the cell that changes

Private Sub worksheet_change(ByVal target As Excel.Range)
Dim vrange As Range
Set vrange = Range("c8")
If Union(target, vrange).Address = vrange.Address Then
Call COPTOVALUES
End If

COPTOVALUES should be the code that copies the values to another cell.
That does not have to be added to the sheet code. That can be an ordinary module

Also go here.

http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/Trackworksheetchangeevent.htm

[raybo]

I think Husker's got the right idea.

When you enter an "IF" statement into a cell, you have to state a value for both the "true" scenario and the "false" scenario. Therefore, once the scenario becomes true and then it becomes false the value in the cell must change to the "false" value. The only way around that, off the top of my head, would be to link the target cell to another cell, like Husker suggested, then when the target cell becomes false, the linked cell will still have the true value in it. So, if you're tracking MTP several times you would need several linked cells to preserve all the different MTPs you're tracking.

Or

How about this?

=IF(MTP>2,"",displayed odds)

Meaning if MTP has not reached 2 minutes yet, then the cell is blank, once it reaches 2 MTP then the cell becomes the displayed odds and never changes again until MTP is once again greater than 2 (when the next race tote starts)

[hcap]

More on events..

http://www.mvps.org/dmcritchie/excel/event.htm

Look at the Timestamp stuff

[punteray]

I will investigate the code given and look at the websites given for additional info!!!

Thank you all:):)

Ray Scalise