IF statement in excel

[SkyHigh]

Hi Folks

I am trying to use the "IF" statment in excel, it works upto 8 steps and I need 10, can you guys please tell me how I can do it, any workaround to it etc, your help is much appreciated.

=IF($B$4=1,BQ11,
IF($B$4=2,BX11,
IF($B$4=3,CE11,
IF($B$4=4,CL11,
IF($B$4=5,CS11,
IF($B$4=6,CZ11,
IF($B$4=7,DG11,
IF($B$4=8,DN11))))))))

Thanks
Brenda

 

[SkipVought]

Hi Sky!

Here's an approch using the INDIRECT worksheet function.

1. Name each of your target cells Data1, Data2, etc, corresponding to 1 to 10.

2. Here's the formula

=INDIRECT("Data"&$B$4)

VOLA!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

There are many ways to handle this.

If your data is really located where you have indicated, then this formula should give you what you want:
[blue]

   =INDEX(BK11:EP11,1,$B$4*7)

[/color]
Another option is to use something like this (fill in the rest of the values):
[blue]

   =CHOOSE($B$4,BQ11,BX11,CE11,CL11,CS11,....)

[/color]
It is also possible to use the INDIRECT function if you prefer. You can also use the IF statement by building your tests in a binary tree structure.

 

[SkyHigh]

Guys, thanks for your replies, I would appreciate if you can explain it to me in steps or if you can convert my above logic into what you are suggesting, as I am absolutely dummy in excel.

Rgds
Brenda

 

[SkyHigh]

How can I refer to a cell in excel using VBA, whats wrong with my following function, as you can see what I am trying to do is if I change the value in Cell B4, it should change the value in B11 to the value in Cell as specified in the function:

Function ChangeVal()
If B4 = 1 Then B11 = BQ11
If B4 = 2 Then B11 = BX11
If B4 = 3 Then B11 = CE11
If B4 = 5 Then B11 = CL11
End If
End If
End If
End Function


Please help me with this.

Thanks
Brenda

 

[SkipVought]

Function ChangeVal()
  If B4 = 1 Then [B11] = [BQ11]
   If B4 = 2 Then [B11] = [BX11]
    If B4 = 3 Then [B11] = [CE11]
     If B4 = 5 Then [B11] = [CL11]
     End If
    End If
   End If
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

oops...

Function ChangeVal()
  If [B4] = 1 Then [B11] = [BQ11]
   If [B4] = 2 Then [B11] = [BX11]
    If [B4] = 3 Then [B11] = [CE11]
     If [B4] = 5 Then [B11] = [CL11]
     End If
    End If
   End If
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkyHigh]

Thanks Skip, now how can I use it, if paste ChangeVal() in cell B11 I get #NAME? error.

Thanks buddy,
Brenda

 

[SkipVought]

Really, what you want is...

Function ChangeVal(rng As Range)
    Select Case rng.Value
    Case 1
        ChangeVal = [BQ11]
    Case 2
        ChangeVal = [BX11]
    Case 3
        ChangeVal = [CE11]
    Case 5
        ChangeVal = [CL11]
    End Select
End Function

where rng is your B4 reference


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkyHigh]

Thanks Skip, it works now, your help is indeed appreciated.
Brenda

 

[SkipVought]

Sorry I wan't paying attention earlier.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Brenda: Very interesting. The final result is practically identical to the use of the built-in CHOOSE function I suggested earlier in this thread. Did you ever look that up in the help file?

Using "hard coded" cell references in a User-Defined function that way can lead to difficulties later on. For example, if you insert a row before row 11, the references in the function will not update automatically. It may not be obvious because the function will not get recalculated until you change the value in the reference cell ($B$4 from your earlier examples) so everything will look ok for awhile. Similarly if you insert/delete columns anywhere in the first 100 or so columns, the formula will begin to pick up values other than what you expect.

Try it yourself if you don't believe me.

Skip, you usually don't let people get away with that sort of thing. It must be Friday.

 

[SkipVought]

Z,

U R correct!

AND...

I am loosing focus. I need to get outta here!

It's 105^oF outside! Bllllaaaahhhhhh!!!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[lespaul]

Skip,

You must be in the Southwest, it's only gotten to around 100 here for the past week! I'm so tired of the heat! I may use your jacuzzi function myself to UNFRY my brain!!

Have a nice weekend!





Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!