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 state
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 work
(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 be
alble to do this with ALL area codes. Any help would be appreciated!
Thanks!


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



--
JPP

[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

[Spencer101]

JPP;1607367 Wrote:
> Thanks a bunch Spencer!

Is this what you had in mind?

I've made a few changes to the setup, but nothing drastic.
All changes made are mentioned in my notes (in red in the file).

This actually would be no more difficult using the zip code....


+-------------------------------------------------------------------+
|Filename: Copy of November Sales2.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=681|
+-------------------------------------------------------------------+



--
Spencer101

[JPP]

JPP;1607374 Wrote:
> One more question Spencer, can this code be modified to work with AND
> 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.


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



--
JPP