Maximun IF statements in a cell
 

Hello,
I have Column C that contains about 110 different material
groups EX, BC1,FF1,FF3,FD1, ext upto 110. I want to create
a formula in B, which this is Sample:
=IF(OR(C:C="FI1",C:C="FI2",C:C="FM1"),"NAME1",IF(O R
(C:C="FX5",C:C="FX6",C:C="FXC",C:C="FXR"),"NAME2", IF(OR
(C:C="FV1"),"NAME3",IF(OR(C:C="BC3",C:C="FV5"),"NA ME4"))))
It works fine but when I add more IF(OR i get an error.
I'm able to have just 7-8 IF(OR so is this the Max in a
Cell?
I think I need about 24 IF(OR . The file that I open
might not include all groups, but changes. So my data file
might contain some groups this month but not all. So when
I do the formula, I want to be able to look for all groups.
Any help would be very greateful.
Thanks,
JUAN

Maximun IF statements in a cell
 

My recommendation would be to use a VLOOKUP formula. First
you must create a table that maps a name to each Material
group, something like (on Sheet2):

A B
1 FI1 NAME1
2 FI2 NAME1
3 FM1 NAME1
4 FX5 NAME2
5 FX6 NAME2
6 FXC NAME2

Then use:

=VLOOKUP(A1,Sheet2!A:B,2,0)

HTH
Jason
Atlanta, GA


-----Original Message-----
Hello,
I have Column C that contains about 110 different
material
groups EX, BC1,FF1,FF3,FD1, ext upto 110. I want to
create
a formula in B, which this is Sample:
=IF(OR(C:C="FI1",C:C="FI2",C:C="FM1"),"NAME1",IF( OR
(C:C="FX5",C:C="FX6",C:C="FXC",C:C="FXR"),"NAME2" ,IF(OR
(C:C="FV1"),"NAME3",IF(OR(C:C="BC3",C:C="FV5"),"N AME4"))))
It works fine but when I add more IF(OR i get an error.
I'm able to have just 7-8 IF(OR so is this the Max in a
Cell?
I think I need about 24 IF(OR . The file that I open
might not include all groups, but changes. So my data
file
might contain some groups this month but not all. So when
I do the formula, I want to be able to look for all
groups.
Any help would be very greateful.
Thanks,
JUAN
.

Maximun IF statements in a cell
 

Hello Jason,
thanks. I did a test and seems to work. Now what would be
the easy way to add this sheet to my other file? I'm
trying to do Macros and stuff so that users dont' do much.
Users will download a file from our system and then run
the macro. Since this VLOOK table will be in another file,
I want to copy this shee to the file that users download
using a macro.
Please advise if you have any idea.

Thanks for your help,
juan
-----Original Message-----
My recommendation would be to use a VLOOKUP formula.
First
you must create a table that maps a name to each Material
group, something like (on Sheet2):

A B
1 FI1 NAME1
2 FI2 NAME1
3 FM1 NAME1
4 FX5 NAME2
5 FX6 NAME2
6 FXC NAME2

Then use:

=VLOOKUP(A1,Sheet2!A:B,2,0)

HTH
Jason
Atlanta, GA


-----Original Message-----
Hello,
I have Column C that contains about 110 different
material
groups EX, BC1,FF1,FF3,FD1, ext upto 110. I want to
create
a formula in B, which this is Sample:
=IF(OR(C:C="FI1",C:C="FI2",C:C="FM1"),"NAME1",IF (OR
(C:C="FX5",C:C="FX6",C:C="FXC",C:C="FXR"),"NAME2 ",IF(OR
(C:C="FV1"),"NAME3",IF(OR
(C:C="BC3",C:C="FV5"),"NAME4"))))
It works fine but when I add more IF(OR i get an error.
I'm able to have just 7-8 IF(OR so is this the Max in a
Cell?
I think I need about 24 IF(OR . The file that I open
might not include all groups, but changes. So my data
file
might contain some groups this month but not all. So
when
I do the formula, I want to be able to look for all
groups.
Any help would be very greateful.
Thanks,
JUAN
.

.

Maximun IF statements in a cell
 

"JUAN" wrote in message
...
Hello,
I have Column C that contains about 110 different material
groups EX, BC1,FF1,FF3,FD1, ext upto 110. I want to create
a formula in B, which this is Sample:
=IF(OR(C:C="FI1",C:C="FI2",C:C="FM1"),"NAME1",IF(O R
(C:C="FX5",C:C="FX6",C:C="FXC",C:C="FXR"),"NAME2", IF(OR
(C:C="FV1"),"NAME3",IF(OR(C:C="BC3",C:C="FV5"),"NA ME4"))))
It works fine but when I add more IF(OR i get an error.
I'm able to have just 7-8 IF(OR so is this the Max in a
Cell?

Yes - a maximum of 7 nested IF functions in one formula. Check out:
http://www.cpearson.com/excel/nested.htm

Maximun IF statements in a cell
 

You could create a UserForm that has all the choices hard
coded into a drop down list. After the user selects a
desired item a routine would be run to go to the next
step in the process.

For example:

Private Sub UserForm_Initialize()
FORM_One.cmbTable.AddItem "Choice1"
FORM_One.cmbTable.AddItem "Choice1"
FORM_One.cmbTable.AddItem "Choice1"

End Sub

Private Sub cmbTable_Click()
Whatever you want to happen after they make a selection.
End Sub

You'll have to look into adding forms and controls.

Hope this helps,
Raul
-----Original Message-----
Hello Jason,
thanks. I did a test and seems to work. Now what would
be
the easy way to add this sheet to my other file? I'm
trying to do Macros and stuff so that users dont' do
much.
Users will download a file from our system and then run
the macro. Since this VLOOK table will be in another
file,
I want to copy this shee to the file that users download
using a macro.
Please advise if you have any idea.

Thanks for your help,
juan
-----Original Message-----
My recommendation would be to use a VLOOKUP formula.
First
you must create a table that maps a name to each
Material
group, something like (on Sheet2):

A B
1 FI1 NAME1
2 FI2 NAME1
3 FM1 NAME1
4 FX5 NAME2
5 FX6 NAME2
6 FXC NAME2

Then use:

=VLOOKUP(A1,Sheet2!A:B,2,0)

HTH
Jason
Atlanta, GA


-----Original Message-----
Hello,
I have Column C that contains about 110 different
material
groups EX, BC1,FF1,FF3,FD1, ext upto 110. I want to
create
a formula in B, which this is Sample:
=IF(OR(C:C="FI1",C:C="FI2",C:C="FM1"),"NAME1",I F(OR
(C:C="FX5",C:C="FX6",C:C="FXC",C:C="FXR"),"NAME 2",IF(OR
(C:C="FV1"),"NAME3",IF(OR
(C:C="BC3",C:C="FV5"),"NAME4"))))
It works fine but when I add more IF(OR i get an
error.
I'm able to have just 7-8 IF(OR so is this the Max in
a
Cell?
I think I need about 24 IF(OR . The file that I
open
might not include all groups, but changes. So my data
file
might contain some groups this month but not all. So
when
I do the formula, I want to be able to look for all
groups.
Any help would be very greateful.
Thanks,
JUAN
.

.

.

Maximun IF statements in a cell
 

Thanks all for the help.
Juan
-----Original Message-----
"JUAN" wrote in
message
...
Hello,
I have Column C that contains about 110 different
material
groups EX, BC1,FF1,FF3,FD1, ext upto 110. I want to
create
a formula in B, which this is Sample:
=IF(OR(C:C="FI1",C:C="FI2",C:C="FM1"),"NAME1",IF(O R
(C:C="FX5",C:C="FX6",C:C="FXC",C:C="FXR"),"NAME2", IF(OR
(C:C="FV1"),"NAME3",IF(OR
(C:C="BC3",C:C="FV5"),"NAME4"))))
It works fine but when I add more IF(OR i get an error.
I'm able to have just 7-8 IF(OR so is this the Max in a
Cell?

Yes - a maximum of 7 nested IF functions in one formula.
Check out:
http://www.cpearson.com/excel/nested.htm


.