Why it doesn´t work?

[cjbr54]

I have this in Cell M9 (odds):
=IF(L9="BACK";G9;IF(L9="LAY";H9;""))

Depending the previous conditions, "BACK" or "LAY" appeared automatically in L9, so I think that´s not the problem.

What I´m doing wrong? Why the bet is not launched?

I appreciate your comments.

[mugsgame]

Is it because you are using ; instead of , ?

[Euler]

That's probably european convention, ';' instead of ','

[cjbr54]

Yes, ";" it´s because is european convention.

Really I don´t see what´s happening. any other way to formulate the same instrucctions with other commands?

[OnGoldWires]

You do not include the formula you are using in L9, but one thing you can check is that you are not putting "BACK " and "LAY " in the cells, because Excel differentiates between "BACK " and "BACK", etc.

[cjbr54]

Hi again,

Here is the sheet if any of you want to take a look.

It has several things:
- VB in Sheet 2 to copy odds 10 min. before the event + the table you can see.
- The rest of things are in the Bet Angel Sheet.

If there is something I´m missing or doing wrong, please provide me some guidance.

Thanks in advance.

[Wyndon]

If I type BACK or LAY in L9, the odds do appear in M9.

If I type BACK in I3 and put =I3 in L9 it also works.

Your use of the AND function in I3 looks incorrect to me. I would put =IF(AND(Sheet2!D4<50000,(Sheet2!D4>0) etc.) But if I do that and put E7 in Sheet 2 to say 3.6 (to satisfy condition), although BACK appears in L9 there is no price in M9. I think it's because of your use of &" "& in the middle of the formula. If I truncate it to just =IF(AND(Sheet2!D4<50000,Sheet2!D4>0),IF(Sheet2!E7>Sheet2!C7*1.02,"BACK","")) it works fine. I think you would need to include your LAY condition as a nested IF within the main body.

[Wyndon]

PS I'd be interested in viewing the VB that records prices x minutes before the off, if you're willing to share.

[cjbr54]

Hi Wyndon,

Here is the VB, I tried several options but this one is the only that worked:

Private Sub Worksheet_Calculate()
If Range("D4") = Range("F4") Then
Range("E7:F8").Select
Selection.Copy
Range("C7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
End Sub

I´m not able to make it works even using yours:
=IF(AND(Sheet2!D4<50000,Sheet2!D4>0),IF(Sheet2!E7>Sheet2!C7*1.02,"BACK","")) Of course I adapt it to European format.

How you would you write the entire instruction? (when you talk about "I think you would need to include your LAY condition as a nested IF within the main body"

Thanks for your time on this.

[Wyndon]

Hi cjbr,

Thanks for the code.

I think the problem must be in using European format.

I put E7 on Sheet 2 to 3.6 and then copied the formula in your reply to I3 and it worked.

Perhaps you can show it in European format and someone with knowledge of that can comment.

[cjbr54]

I think I´m getting closer...

If I write manually "back" and press enter button it works but if it just appeared, as a result of the previous order, it seems it doesn´t recognize it.

Any idea?

[Wyndon]

I think that your use of &" "& in the formula is adding a space at the end of BACK which you can't see and it is this that is causing the problem.

[cjbr54]

Thats it Wyndon!!!!



I owe you one

It must be written like this:
...&""&IF...

[Wyndon]

You're welcome!

Does the whole thing work now?