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

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$1 0,"CA",”&”IF(C3=Legend!$C$11,"TX",”&”IF(C3=Legend! $C$12,"NY",”&”IF(C3=Legend!$C$13,"CA",”&”IF(C3=Leg end!$C$14,"TX",”&”IF(C3=Legend!$C$15,"PA",”&”IF(C3 =Legend!$C$16,"OH",”&”IF(C3=Legend!$C$17,"IL",”&”I F(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=Le gend!$C$27,"MD",”&”IF(C3=Legend!$C$28,"MI",”&”IF(C 3=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=Legen d!$C$37,"MI",”&”IF(C3=Legend!$C$38,"KY",”&”IF(C3=L egend!$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!

This would be far better suited to a VLOOKUP.

If you need help with that, let me know.

Spencer.

I would appreciate the help yes. I have started to attempt that function, but I haven’t had a lot of experience using the VLOOKUP. Thanks!!!

Have a look at the attached for a cut down version of what you're trying to do.

Let me know if it doesn't make sense or you need more help.

S.

That's very neat Spencer. On quick question though, this phone number is in a pre-populated list of information already. Has customer name, address, phone etc. I need to be able to drop code in the (STATE ABBV) cell that auto fills the state abbreviation all the way down after pasting the info into excel. So using the drop down might not work. Example :

John S. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
John D. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
John A. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
John V. Smith | 123 Any St. | Anytown | (123) 234-4567 | (STATE ABBV)
*REPEAT 1,000's of Times*

The "|" character represents a new cell in Excel.
Please see the attached example. Thanks!!!!!!

Could you provide an example workbook? Either here or to the email address I provided.

You can use dummy data to anonymise it but the setup of your workbook will make it quicker to give you a working solution.

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....

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.

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

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.

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

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

All figured out then?