I have series of manipulations on student data pulled from my SIS in order to create the import file for creating accounts in our AD., The line with which I'm suddenly having issues is the nested IF statement I'm using to create the Description field for AD. This formula checks the Grade in Column A, then based on that value, it uses CONCATENATE to generate either the Class of with the DOB for the student. We use this information for certain other processes in the district. Where I'm having the issue is when the formula hits PKA or PKP in the Grades column. We have two separate Pre-K sessions, and students are divided into the morning or afternoon sessions. When the formula hits one of those grade codes, it's supposed to change that to "Pre-K AM" or "Pre-K PM" and then add the DOB for the student. In the past, the formula would generate "AM Pre-K" and "PM Pre-K", respectively, and this worked just fine, no errors. The schools have asked to have this swapped so all the Pre-K kids show up in AD together when sorted by Description. This change in the formula, however, causes Escel to now treat the "pre-K" as a formula, and I have no idea why
My formula:
=IF(A2="PKA",CONCATENATE(“Pre-K AM”,", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"") &IF(A2="PKP",CONCATENATE("Pre-K PM”,", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")& IF(A2="PK",CONCATENATE("Pre-K",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2="K",CONCATENATE("Class of 2036",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=1,CONCATENATE("Class of 2035",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=2,CONCATENATE("Class of 2034",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=3,CONCATENATE("Class of 2033",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=4,CONCATENATE("Class of 2032",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=5,CONCATENATE("Class of 2031",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=6,CONCATENATE("Class of 2030",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=7,CONCATENATE("Class of 2029",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=8,CONCATENATE("Class of 2028",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=9,CONCATENATE("Class of 2027",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=10,CONCATENATE("Class of 2026",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=11,CONCATENATE("Class of 2025",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")&IF(A2=12,CONCATENATE("Class of 2024",", ",TEXT(E2,"00"),"/",TEXT(F2,"00"),"/",TEXT(G2,"0000")),"")
Output from formula:
Grade Description
| 12 | Class of 2024, 01/04/2006 |
| PKP | #NAME? |
From what I can see, Excel is trying to evaluate "Pre-K PM" as three parts to a formula and failing. I cannot for the life of me figure out why "PM Pre-K" works but "Pre-K PM" doesn't. I've tried adding ' to the various parts of the text in quotes, but no love with that.
As a side note, yes, I know that formula is obnoxious, and I'm considering trying to maybe simplify it with a VLOOKUP chart to swap the grade for the Class Of, but I'm not sure how to handle the DOB other than to take what I get from SIS and reformat it by splitting it into TEXT columns and using CONCATENATE to put it back together.
