Specified formula cannot be entered bc it uses more than 64 levels of nesting.

[JPP]

I am trying to setup an IF statement so Excel can auto fill stat
abbreviations that match with the Area Code. I am having some success
until I reach a certian point. I have tried variations of the "&" code
But this only works to a certian point. The code I have that does wor
(up to 67) is as follows :

=IF(C3=Legend!$C$3,"NJ",”&”IF(C3=Legend!$C$4,"DC",”&”IF(C3=Legend!$C$5,"CT",”&”IF(C3=Legend!$C$6,"AL",”&”IF(C3=Legend!$C$7,"WA",”&”IF(C3=Legend!$C$8,"ME",”&”IF(C3=Legend!$C$9,"ID",”&”IF(C3=Legend!$C$10,"CA",”&”IF(C3=Legend!$C$11,"TX",”&”IF(C3=Legend!$C$12,"NY",”&”IF(C3=Legend!$C$13,"CA",”&”IF(C3=Legend!$C$14,"TX",”&”IF(C3=Legend!$C$15,"PA",”&”IF(C3=Legend!$C$16,"OH",”&”IF(C3=Legend!$C$17,"IL",”&”IF(C3=Legend!$C$18,"MN",”&”IF(C3=Legend!$C$19,"IN",”&”IF(C3=Legend!$C$20,"IL",”&”IF(C3=Legend!$C$21,"LA",”&”IF(C3=Legend!$C$22,"MS",”&”IF(C3=Legend!$C$23,"GA",”&”IF(C3=Legend!$C$24,"MI",”&”IF(C3=Legend!$C$25,"OH",”&”IF(C3=Legend!$C$26,"FL",”&”IF(C3=Legend!$C$27,"MD",”&”IF(C3=Legend!$C$28,"MI",”&”IF(C3=Legend!$C$29,"AL",”&”IF(C3=Legend!$C$30,"NC",”&”IF(C3=Legend!$C$31,"WA",”&”IF(C3=Legend!$C$32,"TX",”&”IF(C3=Legend!$C$33,"AL",”&”IF(C3=Legend!$C$34,"IN",”&”IF(C3=Legend!$C$35,"WI",”&”IF(C3=Legend!$C$36,"PA",”&”IF(C3=Legend!$C$37,"MI",”&”IF(C3=Legend!$C$37,"MI",”&”IF(C3=Legend!$C$38,"KY",”&”IF(C3=Legend!$C$39,"VA",”&”IF(C3=Legend!$C$39,"TX"
”&”IF(C3=Legend!$H$3,"MD", ”&”IF(C3=Legend!$H$4,"DE"
”&”IF(C3=Legend!$H$5,"CO", ”&”IF(C3=Legend!$H$6,"WV"
”&”IF(C3=Legend!$H$7,"FL", ”&”IF(C3=Legend!$H$8,"WY"
”&”IF(C3=Legend!$H$9,"NE",”&” IF(C3=Legend!$H$10,"IL"
”&”IF(C3=Legend!$H$11,"CA", ”&”IF(C3=Legend!$H$12,"IL"
”&”IF(C3=Legend!$H$13,"MI", ”&”IF(C3=Legend!$H$14,"MO"
”&”IF(C3=Legend!$H$15,"NY", ”&”IF(C3=Legend!$H$16,"KS"
”&”IF(C3=Legend!$H$17,"IN", ”&”IF(C3=Legend!$H$18,"LA"
”&”IF(C3=Legend!$H$19,"IA", ”&”IF(C3=Legend!$H$20,"MN"
”&”IF(C3=Legend!$H$21,"FL", ”&”IF(C3=Legend!$H$22,"CA"
”&”IF(C3=Legend!$H$23,"TX", ”&”IF(C3=Legend!$H$24,"OH"
”&”IF(C3=Legend!$H$25,"IL", ”&”IF(C3=Legend!$H$26,"AL"
”&”IF(C3=Legend!$H$27,"NC", ”&”IF(C3=Legend!$H$28,"LA"
”&”IF(C3=Legend!$H$29,"MA", ”&”IF(C3=Legend!$H$30,"NY"
”&”IF(C3=Legend!$H$31,"MA", ”&”IF(C3=Legend!$H$32,"FL"
”&”IF(C3=Legend!$H$33,"WA", ”&”IF(C3=Legend!$H$34,"TX"
”&”IF(C3=Legend!$H$35,"UT"
”&”IF(C3=Legend!$H$36,"FL","N/A")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))


I have a legend of all area code setup on the second sheet. I need to b
alble to do this with ALL area codes. Any help would be appreciated
Thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[JPP]

One more question Spencer, can this code be modified to work with AN
without the "()" on the phone number?

(979) 2799555
OR
979279955

Thanks!

Oh! Think I figured it out. Need to change the first 2, to a 1

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Ben McClave]

JPP,

Is there any reason you couldn't use the INDEX/MATCH functions? If your legend has the area codes in one column and the state abbreviations in another, try this:

=INDEX(Legend!$C$3:$D$53, MATCH(C3, Legend!$C$3:$C$53,0), 2)

This assumes that the area code to check is in cell C3, the legend is two columns wide and located at Legend!$C$3:$D$53, and the area codes are in the range Legend!$C$3:$C$53.

 

[GS]

I recommend using a table with 2 cols; col1 for area codes, col2 for
the state abbreviation. Give the table a local defined name like
"'Sheet2'!StateAreaCodes" and use it in a VLOOKUP function.

On Sheet1, give the column where area codes are entered a local defined
name that is col-absolute, row-relative. Example...

Select the first cell in the AreaCodes col.
Open the DefineName dialog and enter "'Sheet1'!AreaCode" for the
name.
Remove the $ symbol between the col lable and row number.
Example for area code col "C"
Name="'Sheet1'!AreaCode" (replace Sheet1 with actual sheetname)
RefersTo=$C1

On sheet1, in the col where you want the state abbreviation...
=VLOOKUP(AreaCode,'Sheet2'!StateAreaCodes,2,False)

...and copy this down as desired.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion