Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Quote:
If you need help with that, let me know. Spencer. |
#3
|
|||
|
|||
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!!!
|
#4
|
|||
|
|||
Quote:
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. |
#5
|
|||
|
|||
Quote:
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!!!!!! |
#6
|
|||
|
|||
Quote:
You can use dummy data to anonymise it but the setup of your workbook will make it quicker to give you a working solution. |
#7
|
|||
|
|||
Thanks a bunch Spencer!
Last edited by JPP : November 13th 12 at 08:28 PM |
#8
|
|||
|
|||
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.... |
#9
|
|||
|
|||
Quote:
(979) 2799555 OR 979279955 Thanks! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
|
|||
|
|||
All figured out then?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
the specified form cannot be entered because it uses more levels of nesting | Excel Worksheet Functions | |||
Excel - exceed nesting levels | Excel Discussion (Misc queries) | |||
Maxing out nesting levels limitation of IF function | Excel Worksheet Functions | |||
30 levels of nesting excel | Excel Worksheet Functions | |||
too many levels of nesting | Excel Worksheet Functions |