Hi guys,
I am trying to trigger a cell to become "active" once any horse in the race reaches a specific back price, I've coded something in excel but the problem is, as the prices drift in and out past the trigger price the cell activates and de-activates constantly.
What I would like it to do is that once any horse in the race reaches my set price the cell is triggered to "active" and the code no longer effects the cell if any horse driffs out again... A bit like the "BACK" code which only works once and then cancels itself.
Is this possible?? And how?
Many thanks.
Excel Trigger Help
I couldn't think of a way of doing this with just a formula. You can do this via the 'Worksheet_Change' event (check all the values and set the appropriate cell), or the a mixture of Worksheet_Change and a formula.
Simple examples are in the attached spreadsheet. They're just examples, but hopefully they'll give you an idea on how to do what you want.
Example 1 - Add a value into cell B8. Then change the values next to the horse names. When one of those values is greater than your trigger value, the formula in cell E2 will show 'Yes'. A worksheet_change event watches for this and then replaces the formula with the text. Click on 'Rearm' to reset the formula.
Example 2 - Far more sensible, simpler, but somehow less interesting. Just do the whole thing in the worksheet_change event. Rearm by just clearing the watch cell (E2).
As I say - just examples on how you might approach the problem. Hope it helps.
Ian
Simple examples are in the attached spreadsheet. They're just examples, but hopefully they'll give you an idea on how to do what you want.
Example 1 - Add a value into cell B8. Then change the values next to the horse names. When one of those values is greater than your trigger value, the formula in cell E2 will show 'Yes'. A worksheet_change event watches for this and then replaces the formula with the text. Click on 'Rearm' to reset the formula.
Example 2 - Far more sensible, simpler, but somehow less interesting. Just do the whole thing in the worksheet_change event. Rearm by just clearing the watch cell (E2).
As I say - just examples on how you might approach the problem. Hope it helps.
Ian
You do not have the required permissions to view the files attached to this post.
Hi Ian,
Just working on the code now and inputting it onto my excel file. I want to have the trigger value lower than the current price instead of higher, at the moment before guardian loads the race information the back columns are blank so the code is activated because this is lower than the trigger price.... can I some how set the trigger value to between 1.01 and 3 for eg to prevent this until it they actually go < 3?
Thanks
Steve.
Just working on the code now and inputting it onto my excel file. I want to have the trigger value lower than the current price instead of higher, at the moment before guardian loads the race information the back columns are blank so the code is activated because this is lower than the trigger price.... can I some how set the trigger value to between 1.01 and 3 for eg to prevent this until it they actually go < 3?
Thanks
Steve.
Hi,
Yes, that would work. Or just add another condition, so that it only triggers if there is some data in the appropriate cells.
In the real spreadsheet (not my example), you could check column 2 (horse name) or columns 8,9,10 (low/middle/high) Lay odds.
If you are using a loop to run down the rows of horses, you can use the field 'Number of runners' (Cell C4), so you aren't going down the whole spreadsheet, but only looking at rows that have runners.
Regards
Ian
Yes, that would work. Or just add another condition, so that it only triggers if there is some data in the appropriate cells.
In the real spreadsheet (not my example), you could check column 2 (horse name) or columns 8,9,10 (low/middle/high) Lay odds.
If you are using a loop to run down the rows of horses, you can use the field 'Number of runners' (Cell C4), so you aren't going down the whole spreadsheet, but only looking at rows that have runners.
Regards
Ian