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