If-Then

[Parrot_Head]

Ok I have a If-Then statement that has more than 7 solutions so obviously a simple formula isn't working.

What I want to do is to create several buttons to several macros to execute this but am getting lost in the coding - if anyone can help please do.

The formula (if it worked) would look like this

=IF(H11="LH",4%,IF(H11="LJ",4%,IF(H11="LK",4%,IF(H11="LL",4%,IF(H11="LM",4%,IF(H11="LN",4%,IF(H11="LO",4%,IF(H11="FALSE",0,0%))))))))

One note - Since H11 was to be copied/paste'd, would like to make it column H.

Thanks,

[Zack Barresse]

Hi there,

Check out the VLOOKUP and LOOKUP functions. If you're looking at doing this via VBA code, you can use If/ElseIf or even a Select Case statement. Other than that, I'm not sure what you're after.

[lucas]

I don't understand....if it worked?

[Parrot_Head]

Lucas - it doesn't work because I have more than 7 solutions. The example I gave isn't all I need

[lucas]

ah....will get back to this asap

[Shazam]

Try...

=IF(OR(H11={"LH","LJ","LK","LL","LM","LN","LO"}),4%,IF(H11="FALSE",0,0%))


Hope it helps!

[Bob Phillips]

What is the difference between 0 and 0%? They are both zero in my book.

[Paul_Hossler]

If you're going to write macros anyway, VBA offers a lot more branching flexability.

One way to stay with worksheet formulas would be to create another WS, which you could hide, with LJ, LK, LL, LM, ...., FALSE etc. in Col A, and 4,4,4,4, ..., 0 in Col B

In you Col H, some VLookup's

=IF(ISERROR(VLOOKUP(H11,HiddenDataSheet!$A:$B,2,FALSE)),"SOMETHING", VLOOKUP(H11,HiddenDataSheet!$A:$B,2,FALSE))

to search Col A on the hidden sheet (called HiddenDataSheet in my example) to search for the value in H11 (e.g. LK) and return the corresponding value from Col B (LK = 4, FALSE = 0, etc.)

Helpful???:think:

Paul

[lucas]

Helpful...I'll say
That allows you to assign whatever value you want to each Set of initals....not just 4%

Keeps the formula short while allowing you to expand the range of data used....

I didn't hide the data sheet for demonstration but I did hide it and test it...works great.

[johnske]

Steve, combine that with VBA and you have much less overhead
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 Then
If Selection.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Formula = "=VLOOKUP(" & Target.Address(0, 0) & ",HiddenDataSheet!$A:$B,2,FALSE)"
If IsError(.Value) Then .ClearContents
.Value = .Value
End With
End If

End Sub

[Parrot_Head]

Sweet - I'll try all and let you know.

Thanks very much!

[lucas]

Works great John.......more alternatives to choose from...

[johnske]

For the percentages... to reduce the overhead of a pre-formatted column change the line .Value = .Value

to .Value = Format(.Value, "##.##%")