Nesting more than nine IF statements in Excel
 

Excel 2000

I am trying to create a sheet which allows us to allocate sectors, and sub
sectors to clients. So for Sony for example, their sector might be
"Electronics" and their sub sector might be "Consumer Electronics". I have
created a list from which people can select the sector, and was using IF
statements within the list function within Data Valiudate to say:

This is in the column to the right of the one with the sector list in

=IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF
(AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF
(AM2="Man",M))))))))

Each returned value (FM, TT, Me...) refers to another list which then shows
the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2
provides a list with Banks, Insurance etc which is taken from another sheet.

This works fine for the above formula but the problem is I need two more
IFs to complete my sectors.

Please help.

--
Message posted via http://www.officekb.com

Hi

=CHOOSE(MATCH(AM2,{"FMT";"T&T";"Med";"H&L";"R&W";" Hospidality and
Leisure";"BSHL";"Man";"xx1";"xx2"},0),TT,Me,HL,RW, HL,BS,M,yy1,yy2)

Btw. HL is returned twice - is it meant so?

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Alexander Walsh via OfficeKB.com" wrote in message
...
Excel 2000

I am trying to create a sheet which allows us to allocate sectors, and sub
sectors to clients. So for Sony for example, their sector might be
"Electronics" and their sub sector might be "Consumer Electronics". I have
created a list from which people can select the sector, and was using IF
statements within the list function within Data Valiudate to say:

This is in the column to the right of the one with the sector list in

=IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF
(AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF
(AM2="Man",M))))))))

Each returned value (FM, TT, Me...) refers to another list which then
shows
the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2
provides a list with Banks, Insurance etc which is taken from another
sheet.

This works fine for the above formula but the problem is I need two more
IFs to complete my sectors.

Please help.

--
Message posted via http://www.officekb.com

Thanks so much, I did not mean to have HL twice.

Basically you have two columns, one with Sector and one with Sub Sector.

Colum AM has lists of 9 sectors, and I want to create a list in AN of Sub
Sectors which depend on what was selected in AM.

When I try the script you gave, Excel tells me I cannot use Arrays in the
Validation thingy.

--
Message posted via http://www.officekb.com

Sounds like this would be better done with a VLOOKUP() table. Put your
'codes' such as FMT and T&T in one column, then put the corresponding name
in the adjacent column. Test your vlookup formula to make sure it works.
Once you are satisfied it's returning the correct name, wrap it in an
INDIRECT() - something like this:

=INDIRECT(VLOOKUP(LookupCode,LookupTable,2,FALSE))

Duke

"Alexander Walsh via OfficeKB.com" wrote in message
...
Excel 2000

I am trying to create a sheet which allows us to allocate sectors, and sub
sectors to clients. So for Sony for example, their sector might be
"Electronics" and their sub sector might be "Consumer Electronics". I have
created a list from which people can select the sector, and was using IF
statements within the list function within Data Valiudate to say:

This is in the column to the right of the one with the sector list in

=IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF
(AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF
(AM2="Man",M))))))))

Each returned value (FM, TT, Me...) refers to another list which then
shows
the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2
provides a list with Banks, Insurance etc which is taken from another
sheet.

This works fine for the above formula but the problem is I need two more
IFs to complete my sectors.

Please help.

--
Message posted via http://www.officekb.com

You can end your exisitng formula with","Not yet""
(AM2="Man",M,"Not yet"))))))))
Insert a helper column, and use this formula:
=IF(<previous column<"Not yet",<previous column,IF(<add your new argument
here,<add your result here,IF(<add your 2nd argument here,<add your 2nd
result here,etc

In this way, you retain the results from the previous column, unless it says
Not yet. All those that have not yet been tested, are now tested. Use the
new column as the source for the next formula, iso the one you are using now.


"Alexander Walsh via OfficeKB.com" wrote:

Excel 2000

I am trying to create a sheet which allows us to allocate sectors, and sub
sectors to clients. So for Sony for example, their sector might be
"Electronics" and their sub sector might be "Consumer Electronics". I have
created a list from which people can select the sector, and was using IF
statements within the list function within Data Valiudate to say:

This is in the column to the right of the one with the sector list in

=IF(AM2="FMT",FM,IF(AM2="T&T",TT,IF(AM2="Med",Me,I F(AM2="H&L",HL,IF
(AM2="R&W",RW, IF(AM2="Hospitality and Leisure",HL,IF(AM2="BSHL",BS,IF
(AM2="Man",M))))))))

Each returned value (FM, TT, Me...) refers to another list which then shows
the sub sectors. So for example if AM2 = FMT (Financial Markets), AN2
provides a list with Banks, Insurance etc which is taken from another sheet.

This works fine for the above formula but the problem is I need two more
IFs to complete my sectors.

Please help.

--
Message posted via http://www.officekb.com

Thanks for this, though I am not sure I understand how to do that.

I have created a column with the "codes" in like FMT, T&T (by the way I
would prefer to use Financial Markets and Technology and Telecoms if
possible) and then made a column next to that with the name codes FM, TT
etc; but I am not sure where to go from there with this vlookup thing.

A

--
Message posted via http://www.officekb.com

One thing I thought of was to make the names of my new list entries the
same as the value in my first list.

So by selecting FMT in the first list, if I Name the second list FMT and
tell it that I was the list source to be the Name of whatever the first
list has got.

I tried this by just entering the source as the first list, so in this case
Source =AM2, but of course that means that it creates a list of just the
value in that cell rather then knowing I want it to use that Named group of
cells. Any ideas?

--
Message posted via http://www.officekb.com

Thanks Kassie, but with your suggestion will I not need one IF statement
for each sector (FMT, T&T etc) either in the help column or within the
Validate source area?

--
Message posted via http://www.officekb.com

I think you simply need to set the source = INDIRECT(AM2)


"Alexander Walsh via OfficeKB.com" wrote in message
...
One thing I thought of was to make the names of my new list entries the
same as the value in my first list.

So by selecting FMT in the first list, if I Name the second list FMT and
tell it that I was the list source to be the Name of whatever the first
list has got.

I tried this by just entering the source as the first list, so in this
case
Source =AM2, but of course that means that it creates a list of just the
value in that cell rather then knowing I want it to use that Named group
of
cells. Any ideas?

--
Message posted via http://www.officekb.com

I have cracked it with the simplest solution ever!

I have the first column (Sector) as a normal list with a load of sectors in
it.

I then have a separate sheet with the sub-sectors in and each is named with
the corresponding name identical to the Sector list above.

In the Select Sub Sector column I then have a very simple thing in the list
source area stating =INDIRECT(<Cell) and this means it takes the value in
the previous one, which corresponds to a name, and pulls the list over.

Job done.!

--
Message posted via http://www.officekb.com

How would I apply this to my problem?

=If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14))))

Obviously there are more arguments than allowed.

"Alexander Walsh via OfficeKB.com" wrote:

I have cracked it with the simplest solution ever!

I have the first column (Sector) as a normal list with a load of sectors in
it.

I then have a separate sheet with the sub-sectors in and each is named with
the corresponding name identical to the Sector list above.

In the Select Sub Sector column I then have a very simple thing in the list
source area stating =INDIRECT(<Cell) and this means it takes the value in
the previous one, which corresponds to a name, and pulls the list over.

Job done.!

--
Message posted via http://www.officekb.com

Hi

=CHOOSE(MATCH(O6,{0;15;16;.......34;35},1),0,0.5,0 .53,.......,0.14,"")
(fill caps in formula)

When there was a typo in your posting, and the last value for O6 was 45
instead of 35, then
=AND(O6<46,O615)*(0.05+0.003*INT(O6-15))


Arvi Laanemets



"Currie" wrote in message
...
How would I apply this to my problem?

=If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14))))

Obviously there are more arguments than allowed.

"Alexander Walsh via OfficeKB.com" wrote:

I have cracked it with the simplest solution ever!

I have the first column (Sector) as a normal list with a load of sectors
in
it.

I then have a separate sheet with the sub-sectors in and each is named
with
the corresponding name identical to the Sector list above.

In the Select Sub Sector column I then have a very simple thing in the
list
source area stating =INDIRECT(<Cell) and this means it takes the value
in
the previous one, which corresponds to a name, and pulls the list over.

Job done.!

--
Message posted via http://www.officekb.com

=CHOOSE(MATCH(O6,{0;15;16;17;18;19;20;21;22;23;24; 25;26;27;28;29;30;31;32;33;34;35},1)*0,0.05,0.053, 0.055,0.058,0.062,0.065,0.069,0.072,0.076,0.081,0. 085,0.09,0.094,0.099,0.0105,0.11,0.116,0.121,0.127 ,0.134,0.14,"")

So there's my formula. I'm not sure what the * is about. I just got a
dialog box that said my formula was incorrect and it made the correction for
me. The value for O6 is 27.19 so the result of the formula should be .094.
If that helps any in trying to figure it out then great!

"Arvi Laanemets" wrote:

Hi

=CHOOSE(MATCH(O6,{0;15;16;.......34;35},1),0,0.5,0 .53,.......,0.14,"")
(fill caps in formula)

When there was a typo in your posting, and the last value for O6 was 45
instead of 35, then
=AND(O6<46,O615)*(0.05+0.003*INT(O6-15))


Arvi Laanemets



"Currie" wrote in message
...
How would I apply this to my problem?

=If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14))))

Obviously there are more arguments than allowed.

"Alexander Walsh via OfficeKB.com" wrote:

I have cracked it with the simplest solution ever!

I have the first column (Sector) as a normal list with a load of sectors
in
it.

I then have a separate sheet with the sub-sectors in and each is named
with
the corresponding name identical to the Sector list above.

In the Select Sub Sector column I then have a very simple thing in the
list
source area stating =INDIRECT(<Cell) and this means it takes the value
in
the previous one, which corresponds to a name, and pulls the list over.

Job done.!

--
Message posted via http://www.officekb.com

Hi

Replace * with comma.


Arvi Laanemets


"Currie" wrote in message
...

=CHOOSE(MATCH(O6,{0;15;16;17;18;19;20;21;22;23;24; 25;26;27;28;29;30;31;32;33
;34;35},1)*0,0.05,0.053,0.055,0.058,0.062,0.065,0. 069,0.072,0.076,0.081,0.08
5,0.09,0.094,0.099,0.0105,0.11,0.116,0.121,0.127,0 .134,0.14,"")

So there's my formula. I'm not sure what the * is about. I just got a
dialog box that said my formula was incorrect and it made the correction
for
me. The value for O6 is 27.19 so the result of the formula should be
..094.
If that helps any in trying to figure it out then great!

"Arvi Laanemets" wrote:

Hi

=CHOOSE(MATCH(O6,{0;15;16;.......34;35},1),0,0.5,0 .53,.......,0.14,"")
(fill caps in formula)

When there was a typo in your posting, and the last value for O6 was 45
instead of 35, then
=AND(O6<46,O615)*(0.05+0.003*INT(O6-15))


Arvi Laanemets



"Currie" wrote in message
...
How would I apply this to my problem?

=If(O6<15,0,if(O6<16,.05,if(O6<17,.053.....,if(O6< 35,.14))))

Obviously there are more arguments than allowed.

"Alexander Walsh via OfficeKB.com" wrote:

I have cracked it with the simplest solution ever!

I have the first column (Sector) as a normal list with a load of
sectors
in
it.

I then have a separate sheet with the sub-sectors in and each is
named
with
the corresponding name identical to the Sector list above.

In the Select Sub Sector column I then have a very simple thing in
the
list
source area stating =INDIRECT(<Cell) and this means it takes the
value
in
the previous one, which corresponds to a name, and pulls the list
over.

Job done.!

--
Message posted via http://www.officekb.com