{"text_format":"1","workbook":"xlsx","n_sheets":23,"sheets":["Group Information","Mid Mkt Enrollment Census IL","Mid Mkt Enrollment IL Mapping","Quoting Census Mapping","SG Enrollment Census Mapping","Source Census From The Group","DetermineSourceColumnAddr","Mid Market Quoting Census","Quoting Census Template","Mid Market Quoting Help File","Quoting Help File","Import Census Template","CensusVersionInfo","ErrData","ACA Help File","Mid Market Enrollment Census","Error List","Mid Mrkt Enrollment Help File","Mid Market Enrollment Help File","DC, OC, WP, PT & TERMED","LGDE Format","MM Enrollment Census Mapping","Census DDL"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":68,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Group Information","index":0,"n_rows":34,"n_cols":8,"rows_kept":34,"cut":false,"hidden":false,"merged":[[0,1,0,4],[25,2,25,4],[26,2,26,4],[33,2,36,4],[1,1,1,4],[27,2,27,4]]}
[null,"Smart Census Import Tool\nSetup Form"]
[]
[null,"Step 1: Please Make a Selection",null,null,"  ***  AHP Pilot R1  ***"]
[null,"Market Segment:","SMALL GROUP",{"$f":"IF(ISBLANK(C4),\"Required: Please select a value.\",IF(rMktSeg=\"SMALL GROUP\",\" (2-50)                                                                 \", \" (51-150)                                                            \"))","v":" (2-50)                                                                 "}]
[null,"Quoting or Enrollment:","QUOTING",{"$f":"IF(ISBLANK(C5),\"Required: Please select a value.\",\"\")","v":""},0]
[null,"Division:",null,{"$f":"IF(ISBLANK(C6),\"Required: Please select a value.\",\"\")","v":"Required: Please select a value."}]
[]
[null,"Step 2: Enter Group Benefits Information"]
[null,"Has Group Selected Health?","Y",{"$f":"IF(ISBLANK(C9),\"Required: Please select a value.\",\"\")","v":""}]
[null,"HMO Plan?","N",{"$f":"IF(AND(C$9=\"N\",C10=\"Y\"),\"Please update: Has Group Selected Health? is N.\",IF(AND(C$9=\"\",C10>\" \",C$6=\"IL\"),\"Has Group Selected Health? is blank.\",IF(AND(C$9=\"Y\",C10=\"\"),\"Required: Please select a value.\",\"\")))","v":""}]
[null,"Plan 1:",null,{"$f":"IF(AND(C$9=\"N\",C11>\" \"),\"Please update: Has Group Selected Health? is N.\",IF(AND(C$9=\"\",C11>\" \"),\"Has Group Selected Health? is blank.\",IF(AND(C$9=\"Y\",rMktSeg=\"SMALL GROUP\",C11=\"\"),\"Required: Please enter a 7 digit plan code.\",IF(AND(C$9=\"Y\",rMktSeg=\"MID MARKET\",C11=\"\"),\"Required: Please enter a plan code.\",\"\"))))","v":"Required: Please enter a 7 digit plan code."}]
[null,"Plan 2:",null,{"$f":"IF(AND(C$9=\"N\",C12>\" \"),\"Please update: Has Group Selected Health? is N.\",IF(AND(C$9=\"\",C12>\" \"),\"Has Group Selected Health? is blank.\",IF(AND(C$9=\"Y\",C12=\"\"),\"Optional: Enter a plan code as needed.\",\"\")))","v":"Optional: Enter a plan code as needed."}]
[null,"Plan 3:",null,{"$f":"IF(AND(C$9=\"N\",C13>\" \"),\"Please update: Has Group Selected Health? is N.\",IF(AND(C$9=\"\",C13>\" \"),\"Has Group Selected Health? is blank.\",IF(AND(C$9=\"Y\",C13=\"\"),\"Optional: Enter a plan code as needed.\",\"\")))","v":"Optional: Enter a plan code as needed."}]
[null,"Plan 4:",null,{"$f":"IF(AND(C$9=\"N\",C14>\" \"),\"Please update: Has Group Selected Health? is N.\",IF(AND(C$9=\"\",C14>\" \"),\"Has Group Selected Health? is blank.\",IF(AND(C$9=\"Y\",C14=\"\"),\"Optional: Enter a plan code as needed.\",\"\")))","v":"Optional: Enter a plan code as needed."}]
[null,"Plan 5:",null,{"$f":"IF(AND(C$9=\"N\",C15>\" \"),\"Please update: Has Group Selected Health? is N.\",IF(AND(C$9=\"\",C15>\" \"),\"Has Group Selected Health? is blank.\",IF(AND(C$9=\"Y\",C15=\"\"),\"Optional: Enter a plan code as needed.\",\"\")))","v":"Optional: Enter a plan code as needed."}]
[null,"Plan 6:",null,{"$f":"IF(AND(C$9=\"N\",C16>\" \"),\"Please update: Has Group Selected Health? is N.\",IF(AND(C$9=\"\",C16>\" \"),\"Has Group Selected Health? is blank.\",IF(AND(C$9=\"Y\",C16=\"\"),\"Optional: Enter a plan code as needed.\",\"\")))","v":"Optional: Enter a plan code as needed."},null,null,null,"`"]
[null,"Has Group Selected Dental?",null,{"$f":"IF(ISBLANK(C17),\"Required: Please select a value.\",\"\")","v":"Required: Please select a value."}]
[null,"Plan 1:",null,{"$f":"IF(AND(C$17=\"N\",C18>\" \"),\"Please update: Has Group Selected Dental? is N.\",IF(AND(C$17=\"\",C18>\" \"),\"Has Group Selected Dental? is blank.\",IF(AND(C$17=\"Y\",rMktSeg=\"SMALL GROUP\",C18=\"\"),\"Required: Please enter a 7 digit plan code.\",IF(AND(C$17=\"Y\",rMktSeg=\"MID MARKET\",C18=\"\"),\"Required: Please enter a plan code.\",\"\"))))","v":""}]
[null,"Plan 2:",null,{"$f":"IF(AND(C$17=\"N\",C19>\" \"),\"Please update: Has Group Selected Dental? is N.\",IF(AND(C$17=\"\",C19>\" \"),\"Has Group Selected Dental? is blank.\",IF(AND(C$17=\"Y\",C19=\"\"),\"Optional: Enter a plan code as needed.\",\"\")))","v":""}]
[null,"Has Group Selected Life? ",null,{"$f":"IF(ISBLANK(C20),\"Required: Please select a value.\",IF(AND(rLifeFlg=\"N\", rSalaryFlg=\"Y\"),\"\",\"\"))","v":"Required: Please select a value."}]
[null,"Is Life or STD   "]
[null,"Salary Based?",null,{"$f":"IF(AND(rLifeFlg=\"Y\",ISBLANK(rSalaryFlg)),\"Required: Please select a value.\",IF(AND(rSalaryFlg=\"Y\",rLifeFlg=\"N\"),\"Please update: Has Group Selected Life? is N.\",\"\"))","v":""}]
[]
[null,"Click Green button for Census Data Entry"]
[null,null,"   Please Note:"]
[null,null,"  -   Census template columns will display/hide\n       based on selections made on this Setup Form.\n  -   Returning to this tab hides Census Template tab\n      and does not overwrite census data."]
[null,null,"  -   If the Group has provided source census on a separate\n      Excel file, click Map Census to copy source data into the\n      census template."]
[]
[]
[]
[]
[]
[null,null,"   ATTENTION:"]
[null,null,"  - Please click this button for displaying all census columns \n     AFTER census data has been entered.\n    "]

{"sheet":"Mid Mkt Enrollment Census IL","index":1,"n_rows":2,"n_cols":38,"rows_kept":2,"cut":false,"hidden":true,"merged":[]}
["Last Name *","Name Middle Initial","First Name *","SSN *","DOB * (mm/dd/yyyy)","Gender * (M or F)","Relationship   (SUB, SPS, DEP, STD, DIS)","Employee SSN\n(SYTEMATICALLY FILLED IF BLANK)","Hire Date* (mm/dd/yyyy)","Employee Status      (A, C, R, T, I,W)","Waiver Reason ","COBRA Reason","COBRA Start Date (mm/dd/yyyy)","COBRA  End Date (mm/dd/yyyy)","Street Line 1","Street Line 2 (Can't duplicate Address 1)","City","State","Zip ","Medicare Eligible  (N,Y)","Medicare  Part A  Effective Date (mm/dd/yyyy)","Medicare Part B Effective Date (mm/dd/yyyy)","Medicare Status  (P,S)","Medicare Reason  (AGED, DIS, ESRD)","Medicare HIC Number","Health Coverage ","Health Coverage Tier","Dental Coverage ","Dental Coverage Tier","WPHCP PCP Number","PCP Number","Medical Group Number","Life Coverage (Y/N)","Different Address for Spouse/Dependents? \nAuto Fills on File Save\nInformational Purpose","Billing Category","Product Category","Suffix","Dental HMO PCP Number"]
[null,null,null,null,null,null,"SUB"]

{"sheet":"Mid Mkt Enrollment IL Mapping","index":2,"n_rows":49,"n_cols":5,"rows_kept":49,"cut":false,"hidden":true,"merged":[]}
["Mid Market Enrollment \n Census Template Columns","Map Group's Source \nCensus Columns\n(Select from drop down list)","Source Census Column Letter\n(For Information Only)",null,"Mapping Source Columns to Census Template Columns"]
["Last Name *",null,{"$f":"IFERROR((VLOOKUP(B2,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column A has column headers from the Census Template tab.\n        Helpful Hint: Click \"+/-\" to the left of row numbers to expand/collapse rows as needed."]
["Name Middle Initial",null,{"$f":"IFERROR((VLOOKUP(B3,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column B has a drop down list with values which are column headers from the source file. \nColumn C is for informational purpose only"]
["First Name *",null,{"$f":"IFERROR((VLOOKUP(B4,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Steps for Mapping:"]
["SSN *",null,{"$f":"IFERROR((VLOOKUP(B5,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"1) In Column B, select a value from the drop down list that corresponds to the value in Column A."]
["DOB * \n(mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B6,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"      Note:  Census Template Columns with a drop down list are denoted by DDL . "]
["Gender * \n(M or F)",null,{"$f":"IFERROR((VLOOKUP(B7,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"                 If columns with DDL are mapped, the value on the source file must match the values in the drop down list.\n                 On the Census Template, File Save data validation will erase values which do not match the drop down list."]
["Relationship ",null,{"$f":"IFERROR((VLOOKUP(B8,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"    If mapping multiple times, Column B values will be stored from previous time. Column B values which do not match the Source Census Header will be highlighted in Yellow. Please update Column B values as needed for the current mapping."]
["\nEmployee SSN\n",null,{"$f":"IFERROR((VLOOKUP(B9,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"2) Click Copy Mapped Columns button to copy the mapped source columns systematically to Census Template tab."]
["Hire Date* \n(mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B10,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"3) Click Cancel button to skip the mapping and go to Census Template tab to enter census."]
["Employee Status\n(A, C, R, T, I,W)",null,{"$f":"IFERROR((VLOOKUP(B11,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Waiver Reason ",null,{"$f":"IFERROR((VLOOKUP(B12,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["COBRA Reason",null,{"$f":"IFERROR((VLOOKUP(B13,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["COBRA Start Date \n(mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B14,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["COBRA end Date Date (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B15,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Street Line 1",null,{"$f":"IFERROR((VLOOKUP(B16,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Street Line 2 ",null,{"$f":"IFERROR((VLOOKUP(B17,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["City",null,{"$f":"IFERROR((VLOOKUP(B18,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["State",null,{"$f":"IFERROR((VLOOKUP(B19,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Zip ",null,{"$f":"IFERROR((VLOOKUP(B20,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Eligible  \n(N,Y)",null,{"$f":"IFERROR((VLOOKUP(B21,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare  Part A  Effective Date (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B22,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Part B Effective Date (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B23,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Status\n(P,S)",null,{"$f":"IFERROR((VLOOKUP(B24,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Reason  \n(AGED, DIS, ESRD)",null,{"$f":"IFERROR((VLOOKUP(B25,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare HIC Number",null,{"$f":"IFERROR((VLOOKUP(B26,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Coverage ",null,{"$f":"IFERROR((VLOOKUP(B27,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Coverage Tier",null,{"$f":"IFERROR((VLOOKUP(B28,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Coverage ",null,{"$f":"IFERROR((VLOOKUP(B29,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Coverage Tier",null,{"$f":"IFERROR((VLOOKUP(B30,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["WPHCP PCP Number",null,{"$f":"IFERROR((VLOOKUP(B31,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["PCP Number",null,{"$f":"IFERROR((VLOOKUP(B32,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medical Group Number",null,{"$f":"IFERROR((VLOOKUP(B33,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Billing Category",null,{"$f":"IFERROR((VLOOKUP(B34,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Product Category",null,{"$f":"IFERROR((VLOOKUP(B35,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Suffix",null,{"$f":"IFERROR((VLOOKUP(B36,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental HMO PCP Number",null,{"$f":"IFERROR((VLOOKUP(B37,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]
[null,null,""]

{"sheet":"Quoting Census Mapping","index":3,"n_rows":112,"n_cols":5,"rows_kept":112,"cut":false,"hidden":true,"merged":[]}
[" Small Group Quoting \nImport Census Template Columns","Map Group's Source \nCensus Columns\n(Select from drop down list)","Source Census Column Letter\n(For Information Only)",null,"Mapping Source Columns to Census Template Columns"]
["Last Name (Case Sensitive)",null,{"$f":"IFERROR((VLOOKUP(B2,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column A has column headers from the Census Template tab."]
["First Name\n(Case Sensitive)",null,{"$f":"IFERROR((VLOOKUP(B3,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column B has a drop down list with values which are column headers from the source file. \nColumn C is for informational purpose only."]
["Relationship Code DDL",null,{"$f":"IFERROR((VLOOKUP(B4,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Steps for Mapping:"]
["Gender (M or F) DDL",null,{"$f":"IFERROR((VLOOKUP(B5,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"1) In Column B, select a value from the drop down list that corresponds to the value in Column A."]
["DOB (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B6,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"      Note:  Census Template Columns with a drop down list are denoted by DDL ."]
["Coverage Type DDL",null,{"$f":"IFERROR((VLOOKUP(B7,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"                 If columns with DDL are mapped, the value on the source file must match the values in the drop down list.\n                 On the Census Template, File Save data validation will erase values which do not match the drop down list."]
["State Code DDL",null,{"$f":"IFERROR((VLOOKUP(B8,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"    If mapping multiple times, Column B values will be stored from previous time. Column B values which do not match the Source Census Header will be highlighted in Yellow. Please update Column B values as needed for the current mapping."]
["Retiree (IL Only) DDL",null,{"$f":"IFERROR((VLOOKUP(B9,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"2) Click Copy Mapped Columns button to copy the mapped source columns systematically to Census Template tab."]
["Annual Salary",null,{"$f":"IFERROR((VLOOKUP(B10,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"3) Click Cancel button to skip the mapping and go to Census Template tab to enter census."]
["Life Class",null,{"$f":"IFERROR((VLOOKUP(B11,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Zip Code (BBF Only)",null,{"$f":"IFERROR((VLOOKUP(B12,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[" Mid Market Quoting \nImport Census Template Columns","Map Group's Source \nCensus Columns\n(Select from drop down list)","Source Census Column Letter\n(For Information Only)",null,"Mapping Source Columns to Census Template Columns"]
["Last Name (Case Sensitive)",null,{"$f":"IFERROR((VLOOKUP(B102,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column A has column headers from the Census Template tab."]
["First Name (Case Sensitive)",null,{"$f":"IFERROR((VLOOKUP(B103,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column B has a drop down list with values which are column headers from the source file. \nColumn C is for informational purpose only."]
["Relationship Code DDL",null,{"$f":"IFERROR((VLOOKUP(B104,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Steps for Mapping:"]
["DOB (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B105,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"1) In Column B, select a value from the drop down list that corresponds to the value in Column A.\n    Note:  Census Template Columns with a drop down list are denoted by DDL ."]
["Gender (M or F) DDL",null,{"$f":"IFERROR((VLOOKUP(B106,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"                 If columns with DDL are mapped, the value on the source file must match the values in the drop down list.\n                 On the Census Template, File Save data validation will erase values which do not match the drop down list."]
["Coverage Type DDL",null,{"$f":"IFERROR((VLOOKUP(B107,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"    If mapping multiple times, Column B values will be stored from previous time. Column B values which do not match the Source Census Header will be highlighted in Yellow. Please update Column B values as needed for the current mapping."]
["State",null,{"$f":"IFERROR((VLOOKUP(B108,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"~  State column applicable to IL Division only."]
["Annual Salary",null,{"$f":"IFERROR((VLOOKUP(B109,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"~  Annual Salary column applicable to OK and NM Divisions."]
["Zip Code (only 5 digits)",null,{"$f":"IFERROR((VLOOKUP(B110,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"2) Click Copy Mapped Columns button to copy the mapped source columns systematically to Census Template tab.\n3) Click Cancel button to skip the mapping and go to Census Template tab to enter census."]
["Medicare Primary (Y, N, U) DDL\n(IL and TX Divisions Only)",null,{"$f":"IFERROR((VLOOKUP(B111,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Employment Status  DDL\n(IL Division Only)",null,{"$f":"IFERROR((VLOOKUP(B112,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]

{"sheet":"SG Enrollment Census Mapping","index":4,"n_rows":68,"n_cols":5,"rows_kept":68,"cut":false,"hidden":true,"merged":[]}
["ACA Small Group Enrollment \nImport Census Template Columns","Map Group's Source \nCensus Columns\n(Select from drop down list)","Source Census Column Letter\n(For Information Only)",null,"Mapping Source Columns to Import Census Template Columns"]
["Group Information Columns"]
["Relationship Code DDL",null,{"$f":"IFERROR((VLOOKUP(B3,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column A has column headers from the Import Census Template.\n        Helpful Hint: Click \"+/-\" to the left of row numbers to expand/collapse rows as needed."]
["Waive All Coverage DDL",null,{"$f":"IFERROR((VLOOKUP(B4,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column B has a drop down list with values which are column headers from the source file. \nColumn C is for informational purpose only."]
["Waive Reason Code DDL",null,{"$f":"IFERROR((VLOOKUP(B5,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Steps for Mapping:"]
["First Name",null,{"$f":"IFERROR((VLOOKUP(B6,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"1) In Column B, select a value from the drop down list that corresponds to the value in Column A."]
["Mid Init",null,{"$f":"IFERROR((VLOOKUP(B7,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"      Note:  Census Template Columns with a drop down list are denoted by DDL . "]
["Last Name",null,{"$f":"IFERROR((VLOOKUP(B8,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"                 If columns with DDL are mapped, the value on the source file must match the values in the drop down list.\n                 On the Import Template, File Save data validation will erase values which do not match the drop down list."]
["Name Suffix",null,{"$f":"IFERROR((VLOOKUP(B9,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"    If mapping multiple times, Column B values will be stored from previous time. Column B values which do not match the Source Census Header will be highlighted in Yellow. Please update Column B values as needed for the current mapping."]
["Date Of Birth",null,{"$f":"IFERROR((VLOOKUP(B10,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"2) Click Copy Mapped Columns button to copy the mapped source columns systematically to Import Census Template tab."]
["Gender DDL",null,{"$f":"IFERROR((VLOOKUP(B11,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"3) Click Cancel button to skip the mapping and go to Import Census Template tab to enter census."]
["SSN",null,{"$f":"IFERROR((VLOOKUP(B12,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Address 1",null,{"$f":"IFERROR((VLOOKUP(B13,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Address 2",null,{"$f":"IFERROR((VLOOKUP(B14,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["City",null,{"$f":"IFERROR((VLOOKUP(B15,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["State DDL",null,{"$f":"IFERROR((VLOOKUP(B16,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Zip Code",null,{"$f":"IFERROR((VLOOKUP(B17,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Home/Cell Phone",null,{"$f":"IFERROR((VLOOKUP(B18,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Business Phone",null,{"$f":"IFERROR((VLOOKUP(B19,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Email Address",null,{"$f":"IFERROR((VLOOKUP(B20,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Marital Status",null,{"$f":"IFERROR((VLOOKUP(B21,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Employment Status DDL",null,{"$f":"IFERROR((VLOOKUP(B22,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Job Title",null,{"$f":"IFERROR((VLOOKUP(B23,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Hire Date",null,{"$f":"IFERROR((VLOOKUP(B24,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Hrs/Week",null,{"$f":"IFERROR((VLOOKUP(B25,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["COBRA Columns"]
["COBRA Reason Code DDL",null,{"$f":"IFERROR((VLOOKUP(B27,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["COBRA Start Date",null,{"$f":"IFERROR((VLOOKUP(B28,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["COBRA Projected End Date",null,{"$f":"IFERROR((VLOOKUP(B29,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["IL Continuation Columns (IL Division Only)"]
["IL Cont Reason Code DDL",null,{"$f":"IFERROR((VLOOKUP(B31,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["IL Cont Start Date",null,{"$f":"IFERROR((VLOOKUP(B32,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["IL Cont Projected End Date",null,{"$f":"IFERROR((VLOOKUP(B33,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Coverage Columns "]
["Health Coverage (Y/N) DDL",null,{"$f":"IFERROR((VLOOKUP(B35,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Coverage Type DDL",null,{"$f":"IFERROR((VLOOKUP(B36,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Selected Health Plan DDL",null,{"$f":"IFERROR((VLOOKUP(B37,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Waive Reason Code DDL",null,{"$f":"IFERROR((VLOOKUP(B38,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Waive Description",null,{"$f":"IFERROR((VLOOKUP(B39,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["HMO Columns "]
["Medical Group/IPA #",null,{"$f":"IFERROR((VLOOKUP(B41,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["PCP #",null,{"$f":"IFERROR((VLOOKUP(B42,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["WPHCP #",null,{"$f":"IFERROR((VLOOKUP(B43,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Coverage Columns"]
["Dental Coverage   (Y/N) DDL",null,{"$f":"IFERROR((VLOOKUP(B45,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Coverage Type DDL",null,{"$f":"IFERROR((VLOOKUP(B46,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Selected Dental Plan DDL",null,{"$f":"IFERROR((VLOOKUP(B47,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Waive Reason Code DDL",null,{"$f":"IFERROR((VLOOKUP(B48,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Waive Description",null,{"$f":"IFERROR((VLOOKUP(B49,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Life Coverage Columns "]
["Life Coverage DDL",null,{"$f":"IFERROR((VLOOKUP(B51,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Job Class Type (2-9 -  Max 3 Life Classes &\n10+  Max 6 Life Classes)",null,{"$f":"IFERROR((VLOOKUP(B52,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Term Life DDL",null,{"$f":"IFERROR((VLOOKUP(B53,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dependent Life DDL ",null,{"$f":"IFERROR((VLOOKUP(B54,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Short Term Disability (STD) DDL",null,{"$f":"IFERROR((VLOOKUP(B55,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Salary",null,{"$f":"IFERROR((VLOOKUP(B56,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Salary Period DDL",null,{"$f":"IFERROR((VLOOKUP(B57,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Information Columns"]
["Medicare Eligible DDL",null,{"$f":"IFERROR((VLOOKUP(B59,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare HIC Number",null,{"$f":"IFERROR((VLOOKUP(B60,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Reason DDL",null,{"$f":"IFERROR((VLOOKUP(B61,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Primary or Secondary DDL",null,{"$f":"IFERROR((VLOOKUP(B62,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Plan A Start Date",null,{"$f":"IFERROR((VLOOKUP(B63,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Plan A End Date",null,{"$f":"IFERROR((VLOOKUP(B64,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Plan B Start Date",null,{"$f":"IFERROR((VLOOKUP(B65,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Plan B End Date",null,{"$f":"IFERROR((VLOOKUP(B66,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Signature Date"]
["Signature Date",null,{"$f":"IFERROR((VLOOKUP(B68,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]

{"sheet":"Source Census From The Group","index":5,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"DetermineSourceColumnAddr","index":6,"n_rows":79,"n_cols":6,"rows_kept":79,"cut":false,"hidden":true,"merged":[]}
["External Column Names","External Column Addresses","Column Letters","Green Button Cntr","Floater1 cntr","Floater2 cntr"]
["Actively emp characteristic (Y/N)","Q","A",1,1,1]
["Category  (Usually for billing - specify only Active or Cobra and with  \"billing catagories\" if warrented )","R","B"]
["Category Product - Wait Period  (Used if there are multi wait periods)","S","C"]
["City","N","D"]
["COBRA Reason   (DV,ET,RT,BS,DS,JT,LO,LS,ME,RH,SS)","H","E"]
["Cobra start date (mm/dd/yyyy)","I","F"]
["Dental Coverage ","X","G"]
["Dental Coverage Tier","Y","H"]
["DOB  (mm/dd/yyyy)","E","I"]
["E-Mail","J","J"]
["Employee SSN  (NO HYPHENS!!!)","U","K"]
["Employee Status (A, C, R)","K","L"]
["First Name ","B","M"]
["Gender (M or F)","F","N"]
["Health Coverage (Chosen plans if more then one - fill all spaces if applicable)","V","O"]
["Health Coverage Tier (fill all spaces)","W","P"]
["HIPAA COCC applied","AS","Q"]
["HIPAA eff Date","AQ","R"]
["HIPAA end date","AR","S"]
["HIPAA-COCC supplied","AP","T"]
["Hire Date  (mm/dd/yyyy)","G","U"]
["Last Name ","A","V"]
["Medical Group Number","AC","W"]
["Medicare  Part A  Effective Date (mm/dd/yyyy)","AE","X"]
["Medicare Eligible  (N,Y)","AD","Y"]
["Medicare HIC Number","AI","Z"]
["Medicare Part B Effective Date (mm/dd/yyyy)","AF","AA"]
["Medicare Reason  (AGED, DIS, ESRD)","AH","AB"]
["Medicare Status  (P,S)","AG","AC"]
["Name Middle Initial","C","AD"]
["Name Prefix","Z","AE"]
["Name Suffix","AA","AF"]
["Other Insurance  (N, U, Y)","AJ","AG"]
["Other Insurance  Dental","AM","AH"]
["Other Insurance  Health","AL","AI"]
["Other Insurance  Hearing","AN","AJ"]
["Other Insurance  Vision","AO","AK"]
["Other Insurance Carrier Name","AK","AL"]
["PCP Number","AB","AM"]
["Relationship   (SUB, SPS,  DEP, STD, DP)","T","AN"]
["SSN (NO HYPHENS!!!)","D","AO"]
["State","O","AP"]
["Street Line 1","L","AQ"]
["Street Line 2 (Can't duplicate Address 1)","M","AR"]
["Zip     (5+4)","P","AS"]
[null,null,"AT"]
[null,null,"AU"]
[null,null,"AV"]
[null,null,"AW"]
[null,null,"AX"]
[null,null,"AY"]
[null,null,"AZ"]
[null,null,"BA"]
[null,null,"BB"]
[null,null,"BC"]
[null,null,"BD"]
[null,null,"BE"]
[null,null,"BF"]
[null,null,"BG"]
[null,null,"BH"]
[null,null,"BI"]
[null,null,"BJ"]
[null,null,"BK"]
[null,null,"BL"]
[null,null,"BM"]
[null,null,"BN"]
[null,null,"BO"]
[null,null,"BP"]
[null,null,"BQ"]
[null,null,"BR"]
[null,null,"BS"]
[null,null,"BT"]
[null,null,"BU"]
[null,null,"BV"]
[null,null,"BW"]
[null,null,"BX"]
[null,null,"BY"]
[null,null,"BZ"]

{"sheet":"Mid Market Quoting Census","index":7,"n_rows":2,"n_cols":12,"rows_kept":2,"cut":false,"hidden":true,"merged":[]}
["Last Name (Case Sensitive)","First Name (Case Sensitive)","Relationship Code","DOB (mm/dd/yyyy)","Age (in Years)","Gender (M or F)","Coverage Type (follow Help file instructions)","State (e.g. IL)","Annual Salary","Zip Code (only 5 digits)","Medicare Primary (Y, N, U)","Employment Status (e.g.A,C,R,T)"]
[null,null,"Employee"]

{"sheet":"Quoting Census Template","index":8,"n_rows":2,"n_cols":11,"rows_kept":2,"cut":false,"hidden":true,"merged":[]}
["Last Name (Case Sensitive)","First Name\n(Case Sensitive)","Relationship Code\n(follow Help file instructions)","Gender (M or F)","DOB (mm/dd/yyyy)","Coverage Type\n(follow Help file instructions)","State Code","Retiree (IL Only)","Annual Salary","Life Class","Zip Code (BBF Only)"]
[null,null,"Employee"]

{"sheet":"Mid Market Quoting Help File","index":9,"n_rows":34,"n_cols":5,"rows_kept":34,"cut":false,"hidden":true,"merged":[[6,1,6,4],[1,1,1,4],[2,1,2,4],[3,1,3,4],[4,1,4,4],[5,1,5,4],[19,1,28,1],[19,3,28,3],[19,4,28,4],[7,1,7,4],[8,1,8,4],[12,1,15,1],[12,3,15,3],[12,4,15,4]]}
[]
[null,"Use these rules to create the Import Census File:"]
[null,"1. Row 1 should not be deleted or changed. "]
[null,"2. Do NOT Delete any column in Census Import tab."]
[null,"3. Do NOT Change the order of the fields."]
[null,"4. Create a row for each census entry, containing these fields entered in this sequence"]
[null,"5. Who should be included on the excel Census? "]
[null,"             Include all employees (and any cobra participants) that would be enrolling in health coverage if the employer chooses BCBS as the health insurer."]
[null,"6. Cells which are highlighted in Red indicate required entry; Cells which are highlighted in Yellow indicate mismatch in data "]
[null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes"]
[null,"Last Name","Alphabetic, numeric, and special characters",20,"Applicable to all divisions\nRequired for Employee, Spouse & Dependent."]
[null,"First Name","Alphabetic, numeric, and special characters",40,"Applicable to all divisions\nRequired for Employee, Spouse & Dependent."]
[null,"Relationship Code","Allowable Values:",9,"Applicable to all divisions\nRequired for Employee, Spouse & Dependent."]
[null,null,"E - Employee"]
[null,null,"S - Spouse"]
[null,null,"D - Dependent"]
[null,"DOB","MM/DD/YYYY",10,"Required for Employee, Spouse & Dependent.\n\nDate of Birth of overage dependents, 26+,  highlighted in Purple Pink for information purposes\n\nDate of Birth of 65 and over age group highlighted in Green for information purposes"]
[null,"Age","Numeric characters only",3,"Column is hidden in the template for all divisions.\nColumn is included in text file for import purposes."]
[null,"Gender","Allowable Values:\nM\nF",1,"Applicable to all divisions\nApplicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nRequired for Employee, Spouse & Dependent."]
[null,"Coverage Type","Allowable Values: IL\nEmp - Employee Only\nEmp+Sp - Employee + Spouse\nEmp+Ch - Employee + Child(ren)","2 or more (depending on division)","Applicable to all divisions\nRequired for Employee."]
[null,null,"Allowable Values: TX, OK, NM, MT\nEO - Employee Only"]
[null,null,"ES - Employee + Spouse"]
[null,null,"EC - Employee + Child(ren)"]
[null,null,"EF - Employee + Family"]
[null,null,"CO - COBRA Employee Only"]
[null,null,"CS - COBRA Employee + Spouse"]
[null,null,"CC - COBRA Employee + Child(ren)"]
[null,null,"CF - COBRA Employee + Family\nDC - Declining Coverage"]
[null,null,"LO - Life Only"]
[null,"State Code (IL Only)","Alphabetic characters only",2,"Applicable to IL only\nRequired for Employee."]
[null,"Annual Salary (OK Only)","Numeric characters only",7,"Applicable to OK only\nColumn is hidden in the template for all divisions.\nColumn is included in text file for import purposes."]
[null,"Zip Code","Numeric characters only",5,"Applicable to all divisions\nRequired for Employee."]
[null,"Medicare Primary","Allowable Values:\nY\nN\nU (TX Only)",1,"Applicable to IL and TX Divisions only\nOptional for Employee."]
[null,"Employment Status (IL Only)","Allowable Values:\nA\nC\nR\nT",1,"Applicable to IL Division only\nRequired for Employee."]

{"sheet":"Quoting Help File","index":10,"n_rows":35,"n_cols":5,"rows_kept":35,"cut":false,"hidden":true,"merged":[[20,1,29,1],[20,3,29,3],[20,4,29,4],[1,1,1,4],[2,1,2,4],[3,1,3,4],[4,1,4,4],[5,1,5,4],[10,1,10,4],[14,1,17,1],[14,3,17,3],[14,4,17,4],[6,1,6,4],[7,1,7,4],[8,1,8,4],[9,1,9,4]]}
[]
[null,"Use these rules to create the Import Census File:"]
[null,"1. Row 1 should not be deleted or changed. "]
[null,"2. Do NOT Delete any column in Census Import tab."]
[null,"3. Do NOT Change the order of the fields."]
[null,"4. Create a row for each census entry, containing these fields entered in this sequence"]
[null,"5. Who should be included on the excel Census? "]
[null,"             Include all employees (and any cobra participants) that would be enrolling in health coverage if the employer chooses BCBS as the health insurer."]
[null,"             Optionally, employees waiving coverage can also be included. Select \"DC\" Coverage Type for employees who are waiving coverage."]
[null,"             Afer File Save validation, a separate Quoting xlsx file will be generated in which employees enrolling in health coverage are included and\n             employees waiving coverage with \"DC\" coverage type are excluded."]
[null,"6. Cells which are highlighted in Red indicate required entry; Cells which are highlighted in Yellow indicate mismatch in data "]
[null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes"]
[null,"Last Name","Alphabetic, numeric, and special characters",20,"Applicable to all divisions\nApplicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nOptional for Employee, Spouse & Dependent."]
[null,"First Name","Alphabetic, numeric, and special characters",20,"Applicable to all divisions\nApplicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nOptional for Employee, Spouse & Dependent."]
[null,"Relationship Code","Allowable Values:",9,"Applicable to all divisions\nApplicablefor 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nRequired for Employee, Spouse & Dependent."]
[null,null,"Employee"]
[null,null,"Spouse"]
[null,null,"Dependent"]
[null,"Gender","Allowable Values:\nM\nF",1,"Applicable to all divisions\nApplicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nRequired for Employee, Spouse & Dependent."]
[null,"Date of Birth","MM/DD/YYYY",10,"Applicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nRequired for Employee, Spouse & Dependent.\n\nDate of Birth of overage dependents, 26+,  highlighted in Purple Pink for information purposes\n\nDate of Birth of 65 and over age group highlighted in Green for information purposes"]
[null,"Coverage Type","Allowable Values: For 1-50 Fully Insured and 10-100 Blue Balanced Funded Quotes",2,"Applicable to all divisions\nApplicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nRequired for Employee\n\n\"DC\" Coverage Type drop down value is available only on this template. The separate Quoting xlsx file that is generated after File Save validation only includes Employees requesting coverage and excludes Employees with \"DC\" coverage type. Please note eSales Quoting Tool does not have \"DC\" Coverage Type value. "]
[null,null,"EO - Employee Only"]
[null,null,"ES - Employee + Spouse"]
[null,null,"EC - Employee + Child"]
[null,null,"EF - Employee + Family"]
[null,null,"CO - COBRA Employee Only"]
[null,null,"CS - COBRA Employee + Spouse"]
[null,null,"CC - COBRA Employee + Child"]
[null,null,"CF - COBRA Employee + Family\nDC - Declining Coverage"]
[null,null,"LO - Life Only (NOT APPLICABLE FOR BLUE BALANCED FUNDED QUOTES)"]
[null,"State Code","Alphabetic characters only",2,"Applicable to all divisions\nApplicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nRequired for Employee"]
[null,"Retiree (IL Only)","Allowable Values:\nY\nN",1,"Applicable to IL Division only\nApplicable for 1-50 Fully Insured & 10-100 Blue Balanced Funded Quote.\nRequired for Employee"]
[null,"Annual Salary","Numeric characters only",7,"Applicable to all divisions\nRequired for Employee\nApplicable only for Fully Insured Quotes of groups with 50 or less total employees and if multiple Life Classes are involved"]
[null,"Life Class","Alphabetic, numeric, and special characters","N/A","Applicable to all divisions\nRequired for Employee\nApplicable only for Fully Insured Quotes of groups with 50 or less total employees and if multiple Life Classes are involved"]
[null,"Zip Code (BBF Only)","Numeric characters only",5,"Applicable to all divisions\nApplicable only for 10-100 Blue Balanced Funded Quote.\nRequired for Employee"]

{"sheet":"Import Census Template","index":11,"n_rows":3,"n_cols":63,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
["General Information",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"If Emp Status is COBRA",null,null,"If Emp Status is IL Cont",null,null,"Health Coverage",null,null,"If Waiving Health Coverage",null,"If HMO Plan is selected",null,null,null,null,null,null,null,"Dental Coverage",null,"",null,null,"Life Coverage",null,null,null,null,null,null,"Medicare ","Information"]
["Relationship Code","Waive All Coverage","Waive Reason Code","First Name","Mid Init","Last Name","Name Suffix","Date Of Birth","Gender","SSN","Address 1","Address 2","City","State","Zip Code","Home/Cell Phone","Business Phone","Email Address","Marital Status","Employment Status","Job Title","Hire Date","Hrs/Week","COBRA Reason Code","COBRA Start Date","COBRA Projected End Date","IL Cont Reason Code","IL Cont Start Date","IL Cont Projected End Date","Health Coverage (Y/N)","Health Coverage Type","Selected Health Plan","Health Waive Reason Code","Health Waive Description","Medical Group/IPA #","Medical Group/IPA Name","PCP #","PCP Name","PCP NPI #","WPHCP #","WPHCP Name","WPHCP NPI #","Dental Coverage (Y/N)","Dental Coverage Type","Selected Dental Plan","Dental Waive Reason Code","Dental Waive Description","Life Coverage (Y/N)","Job Class Type (2-9 -  Max 3 Life Classes &\n10+  Max 6 Life Classes)","Term Life","Dependent Life","Short Term Disability (STD)","Salary","Salary Period","Medicare Eligible","Medicare HIC Number","Medicare Reason","Medicare Primary or Secondary","Plan A Start Date","Plan A End Date","Plan B Start Date","Plan B End Date","Signature Date"]
["Employee"]

{"sheet":"CensusVersionInfo","index":12,"n_rows":5,"n_cols":2,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
["Config Item","Config Value"]
["Census Version","1.0"]
["Division",{"$f":"IF(rDiv=\"IL\",\"Illinois\",(IF(rDiv=\"TX\",\"Texas\",(IF(rDiv=\"OK\",\"Oklahoma\",IF(rDiv=\"NM\",\"New Mexico\",IF(rDiv=\"MT\",\"Montana\",\"\")))))))","v":""}]
["Ready To Upload","Y"]
["Debug","Y"]

{"sheet":"ErrData","index":13,"n_rows":1,"n_cols":2,"rows_kept":1,"cut":false,"hidden":true,"merged":[]}
[null,2]

{"sheet":"ACA Help File","index":14,"n_rows":80,"n_cols":5,"rows_kept":80,"cut":false,"hidden":true,"merged":[[6,1,6,4],[2,1,2,4],[3,1,3,4],[4,1,4,4],[5,1,5,4],[85,1,85,3],[7,1,7,4],[8,1,8,4],[9,1,9,4],[11,1,17,1],[11,3,17,3],[11,4,17,4],[80,1,80,4],[81,1,81,4],[82,1,82,3],[83,1,83,3],[84,1,84,3],[97,1,97,3],[86,1,86,3],[87,1,87,3],[88,1,88,3],[89,1,89,3],[90,1,90,3],[91,1,91,3],[92,1,92,3],[93,1,93,3],[94,1,94,4],[95,1,95,4],[96,1,96,4],[109,1,109,3],[98,1,98,3],[99,1,99,3],[100,1,100,4],[101,1,101,3],[102,1,102,3],[103,1,103,3],[104,1,104,4],[105,1,105,3],[106,1,106,3],[107,1,107,3],[108,1,108,3],[121,1,121,3],[110,1,110,3],[111,1,111,3],[112,1,112,3],[113,1,113,3],[114,1,114,3],[115,1,115,3],[116,1,116,3],[117,1,117,3],[118,1,118,3],[119,1,119,3],[120,1,120,3],[134,1,134,3],[122,1,122,3],[123,1,123,3],[124,1,124,3],[125,1,125,3],[126,1,126,3],[127,1,127,3],[128,1,128,3],[129,1,129,3],[130,1,130,3],[132,1,132,3],[133,1,133,4]]}
[]
[]
[null,"Importing Census Entries:"]
[null,"Creating the Import Census File:"]
[null,"Use these rules to create an Import census file."]
[null,"1. Row 1 should not be deleted or changed. "]
[null,"2. Do NOT Delete any column in Census Import tab."]
[null,"3. Do NOT Change the order of the fields."]
[null,"4. Create a row for each census entry, containing these fields entered in this sequence"]
[null,"5. Who should be included on the excel Census? \n             Include all employees (and any cobra & retired participants) that would be enrolling in health, dental and/or life coverages \n             if the employer chooses BCBS as the health insurer. \n6. Cells which are highlighted in Red indicate required entry; Cells which are highlighted in Yellow indicate mismatch in data \n"]
[null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes"]
[null,"Relationship Code","Allowable Values:",9,"Required for Employee"]
[null,null,"Employee"]
[null,null,"Spouse"]
[null,null,"Domestic Partner"]
[null,null,"Civil Union (IL)"]
[null,null,"Dependent Child"]
[null,null,"Disabled Dependent"]
[null,"Waive All Coverage","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Waive Reason Code","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Waive All Coverage is \"Y\""]
[null,"First Name","Alphabetic, numeric, and special characters",20,"Required for all Relationship Codes"]
[null,"Mid Init","Alphabetic, numeric, and special characters",1,"Optional"]
[null,"Last Name","Alphabetic, numeric, and special characters",20,"Required for all Relationship Codes"]
[null,"Name Suffix","Alphabetic, numeric, and special characters",3,"Optional"]
[null,"Date of Birth","MM/DD/YYYY",10,"Required for all Relationship Codes\n\n\n\n\nDate of Birth of overage dependents, 26+,  highlighted in Purple Pink for information purposes\n\nDate of Birth of 65 and over age group highlighted in Green for information purposes"]
[null,"Gender","Allowable Values:\nM\nF",1,"Required for all Relationship Codes"]
[null,"SSN","Numeric characters only",9,"Required for all Relationship Codes"]
[null,"Address 1","Alphabetic, numeric, and special characters",40,"Required for Employee"]
[null,"Address 2","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"City","Alphabets, hyphen and space",25,"Required for Employee"]
[null,"State","Alphabetic characters only",2,"Required for Employee"]
[null,"Zip Code","Numeric characters only",5,"Required for Employee"]
[null,"Home/Cell Phone","Numeric characters only",10,"Optional"]
[null,"Business Phone","Numeric characters only",10,"Optional"]
[null,"Email Address","Alphabetic, numeric, and special characters",null,"Optional"]
[null,"Marital Status","Allowable Values:\nSingle\nMarried\nDivorced\nWidowed\nDomestic Partner\nCivil Union",null,"Optional"]
[null,"Employment Status","Allowable Values:\nActive\nRetired\nCOBRA\nIL Cont\nIneligible",null,"Required for Employee"]
[null,"Job Title","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"Hire Date","MM/DD/YYYY",10,"Required for Employee"]
[null,"Hrs/Week","Numeric characters only",3,"Optional"]
[null,"COBRA Reason Code","Allowable Values:\nDivorce\nChapter 11 Bankruptcy\nBakruptcy/Surviving Spouse\nLoss of Dependent Status\nJob Termination/Discharge\nLayoff\nLegal Separation\nEmployee's Medicare Entitlement\nReduction in Hours\nJob Term/Discharge - SSD\nLayoff - SSD\nReduction in Hours - SSD\nLoss of Student Status\nEmployee Death\nRetirement\nStudent Comes of Age",null,"Conditional Required:\nA value is required for Employee only if Employment Status is \"COBRA\""]
[null,"COBRA Start Date","MM/DD/YYYY",10,"Conditional Required:\nA value is required for Employee only if Employment Status is \"COBRA\""]
[null,"COBRA Projected End Date","MM/DD/YYYY",10,"Conditional Required:\nA value is required for Employee only if Employment Status is \"COBRA\""]
[null,"IL Cont Reason Code","Allowable Values:\nDependent\nEmployee\nSpouse\nSpouse and Dependent",null,"Conditional Required:\nA value is required for Employee only if Employment Status is \"IL Cont\""]
[null,"IL Cont Start Date","MM/DD/YYYY",10,"Conditional Required:\nA value is required for Employee only if Employment Status is \"IL Cont\""]
[null,"IL Cont Projected End Date","MM/DD/YYYY",10,"Conditional Required:\nA value is required for Employee only if Employment Status is \"IL Cont\""]
[null,"Health Coverage (Y/N)","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Health Coverage Type","Allowable Values for Active, Retired & Ineligible:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child\nEF - Employee + Family\nAllowable Values for COBRA, IL Cont:\nCO - COBRA Employee Only\nCS - COBRA Employee + Spouse\nCC - COBRA Employee + Child\nCF - COBRA Employee + Family",2,"Required for Employee\n\nNote:\nWhen group has selected both Health and Dental, \"DC\" Coverage Type will be systematically added for those employees waiving either Health or Dental coverage."]
[null,"Selected Health Plan","Alphabetic and numeric",7,"Required for Employee"]
[null,"Health Waive Reason Code","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Health Coverage is \"N\""]
[null,"Health Waive Description","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"Medical Group / IPA#","Numeric characters only",3,"Optional"]
[null,"Medical Group / IPA Name","Alphabetic, numeric, and special characters",50,"Optional"]
[null,"PCP#","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"PCP Name","Alphabetic, numeric, and special characters",50,"Optional"]
[null,"PCP NPI#","Numeric characters only",10,"Optional"]
[null,"WPHCP#","Numeric characters only",9,"Optional"]
[null,"WPHCP Name","Alphabetic, numeric, and special characters",50,"Optional"]
[null,"WPHCP NPI#","Numeric characters only",9,"Optional"]
[null,"Dental Coverage","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Dental Coverage Type","Allowable Values for Active, Retired & Ineligible:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child\nEF - Employee + Family\nAllowable Values for COBRA, IL Cont:\nCO - COBRA Employee Only\nCS - COBRA Employee + Spouse\nCC - COBRA Employee + Child\nCF - COBRA Employee + Family",2,"Required for Employee"]
[null,"Selected Dental Plan","Alphabetic and numeric",7,"Required for Employee"]
[null,"Dental Waive Reason Code","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Dental Coverage is \"N\""]
[null,"Dental Waive Description","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"Life Coverage","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Job Class Type (2-9 - Max 3 Life Classes & 10+ Max 6 Life Classes)","Alphabetic, numeric, and special characters",null,"Conditional Required:\nA value is required for Employee only if Life Coverage is \"Y\""]
[null,"Term Life","Allowable Values:\nY\nN",1,"Conditional Required:\nA value is required for Employee only if Life Coverage is \"Y\""]
[null,"Dependent Life","Allowable Values:\nY\nN",1,"Conditional Required:\nA value is required for Employee only if Life Coverage is \"Y\""]
[null,"Short Term Disability (STD)","Allowable Values:\nY\nN",1,"Conditional Required:\nA value is required for Employee only if Life Coverage is \"Y\""]
[null,"Salary","Numeric characters only",9,"Conditional Required:\nA value is required for Employee only if Term Life or STD is salary based"]
[null,"Salary Period","Allowable Values:\nAnnual\nHourly\nMonthly\nSemi-Monthly\nWeekly",null,"Conditional Required:\nA value is required for Employee only if Term Life or STD is salary based"]
[null,"Medicare Eligible","Allowable Values:\nY\nN\nU",1,"Optional"]
[null,"Medicare HIC Number","Alpha numeric characters","minimum 10 \nmax 12 ","Optional"]
[null,"Medicare Reason","Allowable Values:\nAged\nDisabled\nEnd Stage Renal Disease",null,"Optional"]
[null,"Medicare Primary or Secondary","Allowable Values:\nPrimary\nSecondary",null,"Optional"]
[null,"Plan A Start Date","MM/DD/YYYY",10,"Optional"]
[null,"Plan A End Date","MM/DD/YYYY",10,"Optional"]
[null,"Plan B Start Date","MM/DD/YYYY",10,"Optional"]
[null,"Plan B End Date","MM/DD/YYYY",10,"Optional"]
[null,"Signature Date","MM/DD/YYYY",10,"Required for Employee"]

{"sheet":"Mid Market Enrollment Census","index":15,"n_rows":3,"n_cols":46,"rows_kept":3,"cut":false,"hidden":true,"merged":[[0,3,0,4],[0,8,0,9]]}
["\n\n"]
["Last Name ","First Name ","Name Middle Initial","SSN (NO HYPHENS!!!)","DOB  (mm/dd/yyyy)","Gender (M or F)","Hire Date  (mm/dd/yyyy)","COBRA Reason","Cobra start date (mm/dd/yyyy)","E-Mail","Employee Status (A, C, R)","Street Line 1","Street Line 2 (Can't duplicate Address 1)","City","State","Zip     (5+4)","Actively emp characteristic (Y/N)","Category  (Usually for billing - specify only Active or Cobra and with  \"billing catagories\" if warrented )","Category Product - Wait Period  (Used if there are multi wait periods)","Relationship   (SUB, SPS,  DEP, STD, DP)","Employee SSN  (NO HYPHENS!!!)","Health Coverage (Chosen plans if more then one - fill all spaces if applicable)","Health Coverage Tier (fill all spaces)","Dental Coverage ","Dental Coverage Tier","Name Prefix","Name Suffix","PCP Number","Medical Group Number","Medicare Eligible  (N,Y)","Medicare  Part A  Effective Date (mm/dd/yyyy)","Medicare Part B Effective Date (mm/dd/yyyy)","Medicare Status  (P,S)","Medicare Reason  (AGED, DIS, ESRD)","Medicare HIC Number","Other Insurance  (N, U, Y)","Other Insurance Carrier Name","Other Insurance  Health","Other Insurance  Dental","Other Insurance  Hearing","Other Insurance  Vision","HIPAA-COCC supplied","HIPAA eff Date","HIPAA end date","HIPAA COCC applied","Other Insurance Effective Date"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SUB"]

{"sheet":"Error List","index":16,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"Mid Mrkt Enrollment Help File","index":17,"n_rows":158,"n_cols":5,"rows_kept":158,"cut":false,"hidden":true,"merged":[[2,1,2,4],[3,1,3,4],[4,1,4,4],[57,1,57,3],[8,1,8,4],[9,1,9,4],[5,1,5,4],[6,1,6,4],[7,1,7,4],[61,1,61,4],[62,1,62,3],[63,1,63,3],[58,1,58,3],[59,1,59,4],[60,1,60,4],[67,1,67,3],[68,1,68,3],[69,1,69,4],[64,1,64,3],[65,1,65,4],[66,1,66,3],[73,1,73,3],[74,1,74,3],[75,1,75,3],[70,1,70,3],[71,1,71,3],[72,1,72,3],[79,1,79,3],[80,1,80,3],[81,1,81,3],[76,1,76,3],[77,1,77,3],[78,1,78,3],[85,1,85,3],[86,1,86,3],[87,1,87,3],[82,1,82,3],[83,1,83,3],[84,1,84,3],[91,1,91,3],[92,1,92,3],[93,1,93,3],[88,1,88,3],[89,1,89,3],[90,1,90,3],[98,1,98,4],[99,1,99,3],[103,1,103,4],[104,1,104,4],[94,1,94,3],[95,1,95,3],[97,1,97,3],[108,1,108,4],[109,1,109,4],[110,1,110,4],[111,1,111,4],[105,1,105,4],[106,1,106,4],[107,1,107,4]]}
[]
[]
[null,"Mid Market Enrollment Census"]
[]
[null,"Please note:"]
[null,"1. Row 1 should always be a Subscriber/Employee. "]
[null,"2. Do NOT Delete any columns in Census Template tab."]
[null,"3. Do NOT Change the order of the fields."]
[null,"4. Create a row for each census entry - Subscriber, Spouse and Dependent(s)."]
[null,"5. If a Subscriber has Spouse/Dependent(s) rows, these rows must follow the Subscriber's row.\n6. Cells which are highlighted in Red indicate required entry; Cells which are highlighted in Yellow indicate mismatch in data \n"]
[null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes"]
[null,"Last Name","Alphanumeric",20,"Required for all Relationship Codes"]
[null,"First Name ","Alphanumeric",20,"Required for all Relationship Codes"]
[null,"Name Middle Initial","Alphanumeric",1,"Optional"]
[null,"SSN","Numeric","9; No dashes/hyphens","Required for all Relationship Codes"]
[null,"DOB","Numeric",10,"Required for all Relationship Codes\n\nDate of Birth of overage dependents, 26+,  highlighted in Purple Pink for information purposes\n\nDate of Birth of 65 and over age group highlighted in Green for information purposes"]
[null,"Gender (M or F)","Alphanumeric",1,"Required for all Relationship Codes"]
[null,"Hire Date ","MM/DD/YYYY",10,"Required for Subscriber"]
[null,"COBRA Reason","Allowable Values When Employee Status is COBRA \nDV - Divorce\nET-Employee Death\nRT-Retirement\nBS-Bankruptcy/Surviving Spouse\nDS-Loss of Dependent status\nJT-Job Termination/Discharge\nLO-Layoff\nLS-Legal Separation\nME-Employees Medicare Entitlement\nRH-reduction in Hours\nSS-Loss of Student status\n","Drop Down List Available","Required for Subscriber with COBRA"]
[null,"Cobra Start Date ","MM/DD/YYYY",10,"Required for Subscriber with COBRA"]
[null,"Email","Alphanumeric",50,"Optional"]
[null,"Employee Status","Allowable Values:\nA - Active\nC - COBRA\nR - Retired","Drop Down List Available","Required for Subscriber"]
[null,"Address 1","Alphanumeric",25,"Required for all Relationship Codes"]
[null,"Address 2 ","Alphanumeric",25,"Optional\n- Can't duplicate Street Line 1"]
[null,"City","Alphanumeric",25,"Required for all Relationship Codes"]
[null,"State","Alphanumeric",2,"Required for all Relationship Codes"]
[null,"Zip  Code","Numeric",5,"Required for all Relationship Codes"]
[null,"Actively Employed Characteristics","Allowable Values:\nY\nN",1,"Required for Subscriber"]
[null,"Category Billing","Numeric",4,"Required for all Relationship Codes"]
[null,"Category Product","Numeric",4,"Optional"]
[null,"Relationship   ","Allowable Values:\nSUB - Subscriber\nSPS - Spouse\nDEP - Dependent\nSTD - Student\nDP - Domestic Partner, if supported by account","Drop Down List Available","Required for all entries"]
[null,"Employee SSN\n","Numeric","9; No dashes/hyphens","Required for all Relationship Codes\n\nAuto Fill values based on File Save based on Subscriber's SSN."]
[null,"Health Coverage","Alphanumeric","Plan Code","Required for all Relationship Codes"]
[null,"Health Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for all Relationship Codes"]
[null,"Dental Coverage ","Alphanumeric","Plan Code","Required for all Relationship Codes"]
[null,"Dental Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for all Relationship Codes"]
[null,"Name Prefix","Alphanumeric",3,"Optional"]
[null,"Name Suffix","Alphanumeric",3,"Optional"]
[null,"PCP Number","Alphanumeric",10,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber"]
[null,"Medical Group Number","Numeric",3,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber"]
[null,"Medicare Eligible","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional"]
[null,"Medicare  Part A  Effective Date ","MM/DD/YYYY",10,"Conditionally Required:\nRequired if Medicare Eligible is Y"]
[null,"Medicare Part B Effective Date ","MM/DD/YYYY",10,"Optional"]
[null,"Medicare Status ","Allowable Values:\nP - Primary\nS - Secondary","Drop Down List Available","Optional"]
[null,"Medicare Reason  ","Allowable Values:\nAGED - Aged\nDIS - Disabled\nESRD - End Stage Renal Disease","Drop Down List Available","Conditionally Required:\nRequired if Medicare Eligible is Y"]
[null,"Medicare HIC Number","Alphanumeric",12,"Conditionally Required:\nRequired if Medicare Eligible is Y"]
[null,"Other Insurance  \n(N, U, Y)","Allowable Values:\nY\nN",1,"Optional"]
[null,"Other Insurance Carrier Name","Alphabetic, numeric, and special characters",null,"Optional"]
[null,"Other Insurance Health  \n(Y,N)","Allowable Values:\nY\nN",1,"Optional"]
[null,"Other Insurance Dental  \n(Y,N)","Allowable Values:\nY\nN",1,"Optional"]
[null,"Other Insurance Hearing  \n(Y,N)","Allowable Values:\nY\nN",1,"Optional"]
[null,"Other Insurance Vision  \n(Y,N)","Allowable Values:\nY\nN",1,"Optional"]
[null,"HIPAA COCC Supplied\n(Y,N)","Allowable Values:\nY\nN",1,"Optional"]
[null,"HIPAA Effective Date ","MM/DD/YYYY",10,"Optional"]
[null,"HIPAA End Date ","MM/DD/YYYY",10,"Optional"]
[null,"HIPAA Applied\n(Y,N)","Allowable Values:\nY\nN",1,"Optional"]
[null,"Other Insurance Effective Date ","MM/DD/YYYY",10,"Optional"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Mid Market Enrollment Census:"]
[]
[null,"Please note:"]
[null,"1. Row 1 should always be a Subscriber/Employee. "]
[null,"2. Do NOT Delete any columns in Census Template tab."]
[null,"3. Do NOT Change the order of the fields."]
[null,"4. Create a row for each census entry - Subscriber, Spouse and Dependent(s)."]
[null,"5. If a Subscriber has Spouse/Dependent(s) rows, these rows must follow the Subscriber's row.\n"]
[null,"6. Please enter Employee's requesting coverage on the Census Template; Employee's with DC, OC, SP, PT & Termed status should be entered on the corresponding separate tab.\n"]
[null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes"]
[null,"Last Name","Alphanumeric",20,"Required for all Relationship Codes"]
[null,"First Name ","Alphanumeric",20,"Required for all Relationship Codes"]
[null,"Middle Initial","Alphanumeric",1,"Optional"]
[null,"SSN","Numeric","9; No dashes/hyphens","Required for all Relationship Codes"]
[null,"DOB","Numeric",10,"Required for all Relationship Codes\n\nDate of Birth of overage dependents, 26+, and 65 and over age group are highlighted in Purple Pink and Green respectively for information purposes"]
[null,"Gender (M or F)","Alphanumeric",1,"Required for all Relationship Codes"]
[null,"Hire Date ","MM/DD/YYYY",10,"Required for Subscriber only"]
[null,"COBRA Reason","Allowable Values When Employee Status is COBRA \nDV - Divorce\nET-Employee Death\nRT-Retirement\nBS-Bankruptcy/Surviving Spouse\nDS-Loss of Dependent status\nJT-Job Termination/Discharge\nLO-Layoff\nLS-Legal Separation\nME-Employees Medicare Entitlement\nRH-reduction in Hours\nSS-Loss of Student status\n","Drop Down List Available","Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"Cobra Start Date ","MM/DD/YYYY",10,"Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"E-Mail","Alphanumeric",50,"Optional"]
[null,"Employee Status","Allowable Values:\nA - Active\nC - COBRA\nR - Retired","Drop Down List Available"]
[null,"Address 1","Alphanumeric",25,"Required"]
[null,"Address 2","Alphanumeric",25,"Optional\n- Can't duplicate Street Line 1"]
[null,"City","Alphanumeric",25,"Required"]
[null,"State","Alphanumeric",2,"Required"]
[null,"Zip  Code","Numeric",5,"Required"]
[null,"Actively Employed Characteristics","Allowable Values:\nY - Yes\nN - No",1,"Required for Subscriber"]
[null,"Category Billing","Numeric",4,"Required"]
[null,"Category Product","Numeric",4,"Required"]
[null,"Relationship   ","Allowable Values:\nSUB - Subscriber\nSPS - Spouse\nDEP - Dependent\nSTD - Student\nDP  - Domestic Partner (if supported by account)","Drop Down List Available","Required for all entries"]
[null,"Employee SSN\n","Numeric","9; No dashes/hyphens","Required for all Relationship Codes\n\nAuto Fill available - systematically fills in value based on Subscriber's SSN."]
[null,"Health Coverage","Alphanumeric",null,"Required for Subscriber requesting Health Coverage\nDrop down list available; values display per Plans entered on the Setup Form"]
[null,"Health Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for Subscriber requesting Health Coverage"]
[null,"Dental Coverage ","Alphanumeric",null,"Required for Subscriber requesting Dental Coverage\nDrop down list available; values display per Plans entered on the Setup Form"]
[null,"Dental Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for Subscriber requesting Dental Coverage"]
[null,"Name Prefix","Alphanumeric",5,"Optional"]
[null,"Name Suffix","Alphanumeric",3,"Optional"]
[null,"PCP Number","Alphanumeric",20,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber"]
[null,"Medical Group Number","Numeric",3,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber"]
[null,"Medicare Eligible","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional"]
[null,"Medicare  Part A  Effective Date ","MM/DD/YYYY",10,"Optional"]
[null,"Medicare Part B Effective Date ","MM/DD/YYYY",10,"Optional"]
[null,"Medicare Status ","Allowable Values:\nP - Primary\nS - Secondary","Drop Down List Available","Optional"]
[null,"Medicare Reason  ","Allowable Values:\nAGED - Aged\nDIS - Disabled\nESRD - End Stage Renal Disease","Drop Down List Available","Optional"]
[null,"Medicare HIC Number","Alphanumeric",12,"Optional"]
[null,"Other Insurance","Allowable Values:\nY - Yes\nN - No\nU - Unknown","Drop Down List Available","Optional"]
[null,"Other Insurance Carrier Name","Alphanumeric",40,"Optional"]
[null,"Other Insurance Health","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional"]
[null,"Other Insurance Dental","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional"]
[null,"Other Insurance Hearing","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional"]
[null,"Other Insurance Vision","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional"]
[null,"HIPAA-COCC Supplied","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional"]
[null,"HIPAA Effective Date ","MM/DD/YYYY",10,"Optional"]
[null,"HIPAA End Date ","MM/DD/YYYY",10,"Optional"]
[null,"HIPAA Applied ","Allowable Values:\nA - Apply",1,"Optional"]

{"sheet":"Mid Market Enrollment Help File","index":18,"n_rows":155,"n_cols":31,"rows_kept":155,"cut":false,"hidden":true,"merged":[[105,1,105,4],[105,27,105,30],[106,1,106,4],[106,27,106,30],[108,1,108,4],[108,27,108,30],[107,1,107,4],[100,1,100,4],[100,27,100,30],[101,1,101,4],[101,27,101,30],[102,1,102,4],[102,27,102,30],[103,1,103,4],[103,27,103,30],[104,1,104,4],[104,27,104,30],[91,1,91,3],[92,1,92,3],[94,1,94,3],[95,1,95,4],[96,1,96,3],[90,1,90,3],[79,1,79,3],[80,1,80,3],[81,1,81,3],[82,1,82,3],[83,1,83,3],[84,1,84,3],[85,1,85,3],[86,1,86,3],[87,1,87,3],[88,1,88,3],[89,1,89,3],[78,1,78,3],[67,1,67,3],[68,1,68,3],[69,1,69,3],[70,1,70,3],[71,1,71,3],[72,1,72,3],[73,1,73,3],[74,1,74,3],[75,1,75,3],[76,1,76,3],[77,1,77,3],[66,1,66,4],[55,1,55,3],[56,1,56,4],[57,1,57,4],[58,1,58,4],[59,1,59,3],[60,1,60,3],[61,1,61,3],[62,1,62,4],[63,1,63,3],[64,1,64,3],[65,1,65,3],[7,27,7,30],[8,27,8,30],[9,27,9,30],[2,27,2,30],[3,27,3,30],[4,27,4,30],[5,27,5,30],[6,27,6,30],[54,1,54,3],[49,1,49,3],[50,1,50,3],[51,1,51,3],[52,1,52,3],[53,1,53,3],[2,1,2,4],[3,1,3,4],[4,1,4,4],[5,1,5,4],[6,1,6,4],[7,1,7,4],[8,1,8,4],[9,1,9,4],[49,27,49,29],[50,27,50,29],[51,27,51,29],[57,27,57,29],[58,27,58,29],[59,27,59,29],[60,27,60,30],[61,27,61,29],[52,27,52,29],[53,27,53,29],[54,27,54,30],[55,27,55,30],[56,27,56,30],[67,27,67,29],[68,27,68,29],[69,27,69,29],[70,27,70,29],[71,27,71,29],[62,27,62,29],[63,27,63,29],[64,27,64,30],[65,27,65,29],[66,27,66,29],[77,27,77,29],[78,27,78,29],[79,27,79,29],[80,27,80,29],[81,27,81,29],[72,27,72,29],[73,27,73,29],[74,27,74,29],[75,27,75,29],[76,27,76,29],[93,27,93,30],[94,27,94,29],[87,27,87,29],[88,27,88,29],[89,27,89,29],[90,27,90,29],[92,27,92,29],[82,27,82,29],[83,27,83,29],[84,27,84,29],[85,27,85,29],[86,27,86,29]]}
[]
[]
[null,"Mid Market Enrollment Census: Illinois",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Importing Census Entries:"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Creating the Import Census File:"]
[null,"Please note:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Use these rules to create an Import census file."]
[null,"1. Row 1 should always be a Subscriber/Employee. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1. Row 1 should not be deleted or changed. "]
[null,"2. Do NOT Delete any columns in Census Template tab.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2. Do NOT Delete any column in Census Template tab."]
[null,"3. Do NOT Change the order of the fields.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"3. Do NOT Change the order of the fields."]
[null,"4. Create a row for each census entry - Subscriber, Spouse and Dependent(s).",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"4. Create a row for each census entry, containing these fields entered in this sequence"]
[null,"5. If a Subscriber has Spouse/Dependent(s) rows, these rows must follow the Subscriber's row.\n6. Cells which are highlighted in Red indicate required entry; Cells which are highlighted in Yellow indicate mismatch in data \n",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"5. Who should be included on the excel Census? \n             Include all employees (and any cobra & retired participants) that would be enrolling in health, dental and/or life coverages \n             if the employer chooses BCBS as the health insurer. \n"]
[null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes"]
[null,"Last Name","Alphanumeric",20,"Required for all Relationship Codes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Last Name","Alphabets, apostrophe and space",20,"Required for all Relationship Codes"]
[null,"Name Middle Initial","Alphanumeric",1,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"First Name ","Alphabets, hyphen and space",20,"Required for all Relationship Codes"]
[null,"First Name ","Alphanumeric",20,"Required for all Relationship Codes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Name Middle Initial","Alphabets",1,"Optional"]
[null,"SSN","Numeric","9; No dashes/hyphens","Required for Subscriber\nOptional for Spouse and Dependents",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SSN","Numeric",9,"Required for Subscriber\nOptional for Spouse and Dependents"]
[null,"DOB","Numeric",10,"Required for all Relationship Codes\n\nDate of Birth of overage dependents, 26+,  highlighted in Purple Pink for information purposes\n\nDate of Birth of 65 and over age group highlighted in Green for information purposes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"DOB  (mm/dd/yyyy)","Alphabets",3,"Required for all Relationship Codes"]
[null,"Gender (M or F)","Alphanumeric",1,"Required for all Relationship Codes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Gender (M or F)","Alphabets",5,"Required for all Relationship Codes"]
[null,"Relationship   ","Allowable Values:\nSUB - Subscriber\nSPS - Spouse\nDEP - Dependent\nDIS - Disabled Dependent\nDP - Domestic Partner","Drop Down List Available","Required for all entries",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Hire Date  (mm/dd/yyyy)","MM/DD/YYYY",10,"Required for Subscriber"]
[null,"Employee SSN\n","Numeric","9; No dashes/hyphens","Required for all Relationship Codes\n\nAuto Fill available - systematically fills in value based on Subscriber's SSN.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"COBRA/IL Cont Reason","Allowable Values:\nDivorce\nChapter 11 Bankruptcy\nBankruptcy/Surviving Spouse\nLoss of Dependent Status\nJob Termination/Discharge\nLayoff\nLegal Separation\nEmployee's Medicare Entitlement\nReduction in Hours\nJob Term/Discharge - SSD\nLayoff - SSD\nReduction in Hours - SSD\nLoss of Student Status\nEmployee Death\nRetirement\nStudent Comes of Age",null,"Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"Hire Date ","MM/DD/YYYY",10,"Required for Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Cobra/IL Cont Start Date (mm/dd/yyyy)","Numeric characters only; No Hyphens",9,"Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"Employee Status","Allowable Values:\nA - Active\nC - COBRA\nR - Retired\nT - Illinois Continuation\nI - Ineligible\nW- Waive ","Drop Down List Available","Required for Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Address 2 \n(Can't duplicate Address 1)","Alphabetic characters only",2,"Required for Subscriber"]
[null,"Waiver Reason","Allowable Values:\nSpouse/Domestic Partner's Employer Plan\nIndividual Coverage (Non-Group Plan)\nCOBRA/State Continuation\nMedicare or other Government Program\nOther Parent Coverage\nOther Cost\nOther Not Specified","Drop Down List Available","Optional for Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Health Coverage Tier","?","?","?"]
[null,"COBRA Reason","Allowable Values When Employee Status is COBRA \nBankruptcy/Surviving Spouse\nChapter 11 Bankruptcy\nDivorce\nEmployee Death\nEmployee's Medicare Entitlement\nJob Term/Discharge - SSD\nJob Termination/Discharge\nLayoff\nLayoff - SSD\nLegal Separation\nLoss of Dependent Status\nLoss of Student Status\nReduction in Hours\nReduction in Hours - SSD\nRetirement\nStudent Comes of Age\n\nAllowable Values When Employee Status is IL Cont:\nDependent\nEmployee\nSpouse\nSpouse and Dependent\n","Drop Down List Available","Required for Subscriber with COBRA/IL Cont Employee Status",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Cobra/IL Cont End Date (mm/dd/yyyy)","Numeric characters only; No Hyphens",9,"Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"Cobra Start Date ","MM/DD/YYYY",10,"Required for Subscriber with COBRA/IL Cont Employee Status",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"E-Mail","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"Cobra End Date ","MM/DD/YYYY",10,"Required for Subscriber with COBRA/IL Cont Employee Status",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Employee Status","Alphabetic, numeric, and special characters",40,"Required for Subscriber"]
[null,"Street Line 1","Alphanumeric",25,"Required for Subscriber\nOptional for Spouse and Dependents",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"City","Numeric characters only",5,"Required for Subscriber"]
[null,"Street Line 2 ","Alphanumeric",25,"Optional\n- Can't duplicate Street Line 1",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"State","Alphabetic, numeric, and special characters",50,"Required for Subscriber"]
[null,"City","Alphanumeric",25,"Required for Subscriber\nOptional for Spouse and Dependents",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Zip  Code","Allowable Values: TX, OK, NM\nA (Active)\nR (Retired)\nC (COBRA)\n\nAllowable Values: IL\nA (Active)\nR (Retired)\nC (COBRA)\nT (IL Cont)\nI (Ineligible)\nW (Waive)",1,"Required for Subscriber"]
[null,"State","Alphanumeric",2,"Required for Subscriber\nOptional for Spouse and Dependents",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Actively Employed Characteristic \n(Y/N)","Allowable Values: IL\nAS- ARRA Subsidy\nDV- Divorce\nET- Employee Death\nRH- Reduction in Hours\nRT- Retirement\nBN- Chaper 11 Bankruptcy\nBS/ Bankruptcy/ Surviving Spouse\nDS- Loss of Dependent Status\nJT- Job Termination/ Discharge\nLO- Layoff\nLS- Legal Separation\nME- Employee's Medicare Entitlement\nSJ- Job Ter/ Discharge- SSD\nSL- Layoff- SSD\nSR- Reduction in Hours- SSD\nSS- Loss of Student Status\nST- Student Comes of Age\n\nAllowable Values: TX, OK, NM\nDV - Divorce\nET-Employee Death\nRT-Retirement\nBS-Bankruptcy/Surviving Spouse\nDS-Loss of Dependent status\nJT-Job Termination/Discharge\nLO-Layoff\nLS-Legal Separation\nME-Employees Medicare Entitlement\nRH-reduction in Hours\nSS-Loss of Student status\n",2,"Conditional Required:\nA value is required for Employee only if Employment Status is \"COBRA\""]
[null,"Zip  Code","Numeric",5,"Required for Subscriber\nOptional for Spouse and Dependents",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Category  Billing\n(For only Active or Cobra and with  billing catagories)","MM/DD/YYYY",10,"Conditional Required:\nA value is required for Employee only if Employment Status is \"COBRA\""]
[null,"Medicare Eligible","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Medicare  Part A  Effective Date ","MM/DD/YYYY",10,"Conditionally Required:\nRequired if Medicare Eligible is Y",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Status  (P,S)","Numeric characters only",9,"Optional"]
[null,"Medicare Part B Effective Date ","MM/DD/YYYY",10,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Reason  (AGED, DIS, ESRD)","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Dental Coverage is \"N\""]
[null,"Medicare Status ","Allowable Values:\nP - Primary\nS - Secondary","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare HIC /MIB Number","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"Medicare Reason  ","Allowable Values:\nAGED - Aged\nDIS - Disabled\nESRD - End Stage Renal Disease","Drop Down List Available","Conditionally Required:\nRequired if Medicare Eligible is Y",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Other Insurance  \n(N, U, Y)","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Medicare HIC Number","Alphanumeric",12,"Conditionally Required:\nRequired if Medicare Eligible is Y",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Other Insurance Carrier Name","Alphabetic, numeric, and special characters",null,"Conditional Required:\nA value is required for Employee only if Life Coverage is \"Y\""]
[null,"Health Coverage","Alphanumeric",50,"Required for Subscriber requesting Health Coverage\nDrop down list available; values display per Plans entered on the Setup Form",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage ","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Health Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for Subscriber requesting Health Coverage",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage Tier","Spouse/Domestic Partner’s Employer Plan\nIndividual Coverage (Non-Group Plan)\nCOBRA/State Continuation\nMedicare or other Government Program\nOther (Identify)"]
[null,"Dental Coverage ","Alphanumeric",50,"Required for Subscriber requesting Dental Coverage\nDrop down list available; values display per Plans entered on the Setup Form",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Name Prefix",null,1,"Required for all Relationship Codes"]
[null,"Dental Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for Subscriber requesting Dental Coverage",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Name Suffix","Allowable Values for Active, Retired & Ineligible:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child\nEF - Employee + Family\nAllowable Values for COBRA, IL Cont:\nCO - COBRA Employee Only\nCS - COBRA Employee + Spouse\nCC - COBRA Employee + Child\nCF - COBRA Employee + Family",2,"Required for Employee\n\nNote:\nWhen group has selected both Health and Dental, \"DC\" Coverage Type will be systematically added for those employees waiving either Health or Dental coverage."]
[null,"WPHCP PCP Number","Numeric",9,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medical Group Number",null,4,"Required for Employee"]
[null,"PCP Number","Numeric",9,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Eligible  (Y,N)","Allowable Values for Active, Retired & Ineligible:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child\nEF - Employee + Family\nAllowable Values for COBRA, IL Cont:\nCO - COBRA Employee Only\nCS - COBRA Employee + Spouse\nCC - COBRA Employee + Child\nCF - COBRA Employee + Family",2,"Required for Employee"]
[null,"Medical Group Number","Numeric",3,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare  Part A  Effective Date (mm/dd/yyyy)","Numeric characters only",3,"Optional"]
[null,"Life Coverage","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Different Address for Spouse/Dep? \n","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Information purpose only\n- Auto Fills on File Save; Y value is populated if Spouse and Dependent addresses are different than the Subscriber\n",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Billing Category","Alphanumeric",null,"Required for Subscriber with Coverage when Billing Category is applicable",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage ","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Product Category","Alphanumeric",35,"Required for Subscriber with Coverage when Product Category is applicable",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage ","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Suffix","Alphanumeric",3,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage ","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Dental HMO PCP Number","Alphanumeric",null,"Required when Dental HMO Plan is selected",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage ","Allowable Values:\nY\nN",1,"Required for Employee"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Mid Market Enrollment Census:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Importing Census Entries:"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Creating the Import Census File:"]
[null,"Please note:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Use these rules to create an Import census file."]
[null,"1. Row 1 should always be a Subscriber/Employee. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1. Row 1 should not be deleted or changed. "]
[null,"2. Do NOT Delete any columns in Census Template tab.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2. Do NOT Delete any column in Census Template tab."]
[null,"3. Do NOT Change the order of the fields.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"3. Do NOT Change the order of the fields."]
[null,"4. Create a row for each census entry - Subscriber, Spouse and Dependent(s).",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"4. Create a row for each census entry, containing these fields entered in this sequence"]
[null,"5. If a Subscriber has Spouse/Dependent(s) rows, these rows must follow the Subscriber's row.\n"]
[null,"6. Please enter Employee's requesting coverage on the Census Template; Employee's with DC, OC, SP, PT & Termed status should be entered on the corresponding separate tab.\n",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"5. Who should be included on the excel Census? \n             Include all employees (and any cobra & retired participants) that would be enrolling in health, dental and/or life coverages \n             if the employer chooses BCBS as the health insurer. \n"]
[null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Field Name","Formatting Specifications","Maximum Permissible Length","Notes"]
[null,"Last Name","Alphanumeric",20,"Required for all Relationship Codes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Last Name","Alphabets, apostrophe and space",20,"Required for all Relationship Codes"]
[null,"First Name ","Alphanumeric",20,"Required for all Relationship Codes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Name Middle Initial","Alphabets",1,"Optional"]
[null,"Middle Initial","Alphanumeric",1,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"First Name ","Alphabets, hyphen and space",20,"Required for all Relationship Codes"]
[null,"SSN","Numeric","9; No dashes/hyphens","Required for all Relationship Codes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SSN","Numeric",9,"Required for Subscriber\nOptional for Spouse and Dependents"]
[null,"DOB","Numeric",10,"Required for all Relationship Codes\n\nDate of Birth of overage dependents, 26+, and 65 and over age group are highlighted in Purple Pink and Green respectively for information purposes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"DOB  (mm/dd/yyyy)","Alphabets",3,"Required for all Relationship Codes"]
[null,"Gender (M or F)","Alphanumeric",1,"Required for all Relationship Codes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Gender (M or F)","Alphabets",5,"Required for all Relationship Codes"]
[null,"Hire Date ","MM/DD/YYYY",10,"Required for Subscriber only",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Cobra/IL Cont Start Date (mm/dd/yyyy)","Numeric characters only; No Hyphens",9,"Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"COBRA Reason","Allowable Values When Employee Status is COBRA \nDV - Divorce\nET-Employee Death\nRT-Retirement\nBS-Bankruptcy/Surviving Spouse\nDS-Loss of Dependent status\nJT-Job Termination/Discharge\nLO-Layoff\nLS-Legal Separation\nME-Employees Medicare Entitlement\nRH-reduction in Hours\nSS-Loss of Student status\n","Drop Down List Available","Required for Subscriber with COBRA/IL Cont Employee Status",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Cobra/IL Cont End Date (mm/dd/yyyy)","Numeric characters only; No Hyphens",9,"Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"Cobra Start Date ","MM/DD/YYYY",10,"Required for Subscriber with COBRA/IL Cont Employee Status",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"E-Mail","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"E-Mail","Alphanumeric",50,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Employee Status","Alphabetic, numeric, and special characters",40,"Required for Subscriber"]
[null,"Employee Status","Allowable Values:\nA - Active\nC - COBRA\nR - Retired","Drop Down List Available",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Address 2 \n(Can't duplicate Address 1)","Alphabetic characters only",2,"Required for Subscriber"]
[null,"Address 1","Alphanumeric",25,"Required",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"City","Numeric characters only",5,"Required for Subscriber"]
[null,"Address 2","Alphanumeric",25,"Optional\n- Can't duplicate Street Line 1",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"State","Alphabetic, numeric, and special characters",50,"Required for Subscriber"]
[null,"City","Alphanumeric",25,"Required",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Zip  Code","Allowable Values: TX, OK, NM\nA (Active)\nR (Retired)\nC (COBRA)\n\nAllowable Values: IL\nA (Active)\nR (Retired)\nC (COBRA)\nT (IL Cont)\nI (Ineligible)\nW (Waive)",1,"Required for Subscriber"]
[null,"State","Alphanumeric",2,"Required",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Actively Employed Characteristic \n(Y/N)","Allowable Values: IL\nAS- ARRA Subsidy\nDV- Divorce\nET- Employee Death\nRH- Reduction in Hours\nRT- Retirement\nBN- Chaper 11 Bankruptcy\nBS/ Bankruptcy/ Surviving Spouse\nDS- Loss of Dependent Status\nJT- Job Termination/ Discharge\nLO- Layoff\nLS- Legal Separation\nME- Employee's Medicare Entitlement\nSJ- Job Ter/ Discharge- SSD\nSL- Layoff- SSD\nSR- Reduction in Hours- SSD\nSS- Loss of Student Status\nST- Student Comes of Age\n\nAllowable Values: TX, OK, NM\nDV - Divorce\nET-Employee Death\nRT-Retirement\nBS-Bankruptcy/Surviving Spouse\nDS-Loss of Dependent status\nJT-Job Termination/Discharge\nLO-Layoff\nLS-Legal Separation\nME-Employees Medicare Entitlement\nRH-reduction in Hours\nSS-Loss of Student status\n",2,"Conditional Required:\nA value is required for Employee only if Employment Status is \"COBRA\""]
[null,"Zip  Code","Numeric",5,"Required",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Category  Billing\n(For only Active or Cobra and with  billing catagories)","MM/DD/YYYY",10,"Conditional Required:\nA value is required for Employee only if Employment Status is \"COBRA\""]
[null,"Actively Employed Characteristics","Allowable Values:\nY - Yes\nN - No",1,"Required for Subscriber"]
[null,"Category Billing","Numeric",4,"Required"]
[null,"Category Product","Numeric",4,"Required"]
[null,"Relationship   ","Allowable Values:\nSUB - Subscriber\nSPS - Spouse\nDEP - Dependent\nSTD - Student\nDP  - Domestic Partner (if supported by account)","Drop Down List Available","Required for all entries",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Hire Date  (mm/dd/yyyy)","MM/DD/YYYY",10,"Required for Subscriber"]
[null,"Employee SSN\n","Numeric","9; No dashes/hyphens","Required for all Relationship Codes\n\nAuto Fill available - systematically fills in value based on Subscriber's SSN.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"COBRA/IL Cont Reason","Allowable Values:\nDivorce\nChapter 11 Bankruptcy\nBankruptcy/Surviving Spouse\nLoss of Dependent Status\nJob Termination/Discharge\nLayoff\nLegal Separation\nEmployee's Medicare Entitlement\nReduction in Hours\nJob Term/Discharge - SSD\nLayoff - SSD\nReduction in Hours - SSD\nLoss of Student Status\nEmployee Death\nRetirement\nStudent Comes of Age",null,"Required for Subscriber with COBRA/IL Cont Employee Status"]
[null,"Health Coverage","Alphanumeric",null,"Required for Subscriber requesting Health Coverage\nDrop down list available; values display per Plans entered on the Setup Form",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage ","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Health Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for Subscriber requesting Health Coverage",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dental Coverage Tier","Spouse/Domestic Partner’s Employer Plan\nIndividual Coverage (Non-Group Plan)\nCOBRA/State Continuation\nMedicare or other Government Program\nOther (Identify)"]
[null,"Dental Coverage ","Alphanumeric",null,"Required for Subscriber requesting Dental Coverage\nDrop down list available; values display per Plans entered on the Setup Form",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Name Prefix",null,1,"Required for all Relationship Codes"]
[null,"Dental Coverage Tier","Allowable Values:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child(ren)\nEF - Employee + Family ","Drop Down List Available","Required for Subscriber requesting Dental Coverage",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Name Suffix","Allowable Values for Active, Retired & Ineligible:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child\nEF - Employee + Family\nAllowable Values for COBRA, IL Cont:\nCO - COBRA Employee Only\nCS - COBRA Employee + Spouse\nCC - COBRA Employee + Child\nCF - COBRA Employee + Family",2,"Required for Employee\n\nNote:\nWhen group has selected both Health and Dental, \"DC\" Coverage Type will be systematically added for those employees waiving either Health or Dental coverage."]
[null,"Name Prefix","Alphanumeric",5,"Optional"]
[null,"Name Suffix","Alphanumeric",3,"Optional"]
[null,"PCP Number","Alphanumeric",20,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Eligible  (Y,N)","Allowable Values for Active, Retired & Ineligible:\nEO - Employee Only\nES - Employee + Spouse\nEC - Employee + Child\nEF - Employee + Family\nAllowable Values for COBRA, IL Cont:\nCO - COBRA Employee Only\nCS - COBRA Employee + Spouse\nCC - COBRA Employee + Child\nCF - COBRA Employee + Family",2,"Required for Employee"]
[null,"Medical Group Number","Numeric",3,"Conditionally Required:\nRequired if HMO plan is selected by the Subscriber",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare  Part A  Effective Date (mm/dd/yyyy)","Numeric characters only",3,"Optional"]
[null,"Medicare Eligible","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Medicare  Part A  Effective Date ","MM/DD/YYYY",10,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Status  (P,S)","Numeric characters only",9,"Optional"]
[null,"Medicare Part B Effective Date ","MM/DD/YYYY",10,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Reason  (AGED, DIS, ESRD)","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Dental Coverage is \"N\""]
[null,"Medicare Status ","Allowable Values:\nP - Primary\nS - Secondary","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare HIC /MIB Number","Alphabetic, numeric, and special characters",40,"Optional"]
[null,"Medicare Reason  ","Allowable Values:\nAGED - Aged\nDIS - Disabled\nESRD - End Stage Renal Disease","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Other Insurance  \n(N, U, Y)","Allowable Values:\nY\nN",1,"Required for Employee"]
[null,"Medicare HIC Number","Alphanumeric",12,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Other Insurance Carrier Name","Alphabetic, numeric, and special characters",null,"Conditional Required:\nA value is required for Employee only if Life Coverage is \"Y\""]
[null,"Other Insurance","Allowable Values:\nY - Yes\nN - No\nU - Unknown","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Other Insurance Carrier Name","Alphanumeric",40,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Other Insurance Carrier Name","Alphabetic, numeric, and special characters",null,"Conditional Required:\nA value is required for Employee only if Life Coverage is \"Y\""]
[null,"Other Insurance Health","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Other Insurance Dental","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Other Insurance Hearing","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"Other Insurance Vision","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"HIPAA-COCC Supplied","Allowable Values:\nY - Yes\nN - No","Drop Down List Available","Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Part B Effective Date (mm/dd/yyyy)","Alpha numeric characters","9 (IL)\n10 (TX, OK, NM, MT)","Optional"]
[null,"HIPAA Effective Date ","MM/DD/YYYY",10,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Reason  (AGED, DIS, ESRD)","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Dental Coverage is \"N\""]
[null,"HIPAA End Date ","MM/DD/YYYY",10,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Reason  (AGED, DIS, ESRD)","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Dental Coverage is \"N\""]
[null,"HIPAA Applied ","Allowable Values:\nA - Apply",1,"Optional",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Medicare Reason  (AGED, DIS, ESRD)","Allowable Values:\nWaiving - Has other coverage\nWaiving - No other coverage",null,"Conditional Required:\nA value is required for Employee only if Dental Coverage is \"N\""]

{"sheet":"DC, OC, WP, PT & TERMED","index":19,"n_rows":2,"n_cols":3,"rows_kept":2,"cut":false,"hidden":true,"merged":[[0,0,0,3]]}
["Enter employee's name below with their decision only:  Waiting Period (WP), Declined (DC), Other Coverage (OC).   If they were on the originally quoted census and have TERM'D or are Part-time (PT), they need to go here as well."]
["Last Name","First Name","Decision"]

{"sheet":"LGDE Format","index":20,"n_rows":2,"n_cols":33,"rows_kept":2,"cut":false,"hidden":true,"merged":[]}
["Last Name","First Name ","SSN\n (NO HYPHENS!)","DOB  (mm/dd/yyyy)","Gender (M or F)","Hire Date  (mm/dd/yyyy)","Employee Status","Cobra Start Date (mm/dd/yyyy)","COBRA Reason","Address 1","Address 2 \n(Can't duplicate Address 1)","City","State","Zip  Code","Category Product - Wait Period","Category  Billing\n(For Active or Cobra and with  billing catagories)","Name Middle Initial","Relationship","Employee SSN  (NO HYPHENS!!!)","Medicare Eligible  (N,Y)","Medicare  Part A  Effective Date (mm/dd/yyyy)","Medicare Part B Effective Date (mm/dd/yyyy)","Medicare Status  (P,S)","Medicare Reason  (AGED, DIS, ESRD)","Medicare HIC Number","Health Coverage","Health Coverage Tier","Dental Coverage ","Dental Coverage Tier","Medical Group Number","PCP Number","CMSESC","Actively Employed Characteristic \n(Y/N)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SUB"]

{"sheet":"MM Enrollment Census Mapping","index":21,"n_rows":33,"n_cols":5,"rows_kept":33,"cut":false,"hidden":true,"merged":[]}
["Mid Market Enrollment \nImport Census Template Columns","Map Group's Source \nCensus Columns\n(Select from drop down list)","Source Census Column Letter\n(For Information Only)",null,"Mapping Source Columns to Import Census Template Columns"]
["Last Name",null,{"$f":"IFERROR((VLOOKUP(B2,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column A has column headers from the Census Template.\n        Helpful Hint: Click \"+/-\" to the left of row numbers to expand/collapse rows as needed."]
["First Name ",null,{"$f":"IFERROR((VLOOKUP(B3,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Column B has a drop down list with values which are column headers from the source file. \nColumn C is for informational purpose only"]
["Name Middle Initial",null,{"$f":"IFERROR((VLOOKUP(B4,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"Steps for Mapping:"]
["SSN\n (NO HYPHENS!)",null,{"$f":"IFERROR((VLOOKUP(B5,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"1) In Column B, select a value from the drop down list that corresponds to the value in Column A."]
["DOB  (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B6,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"      Note:  Census Template Columns with a drop down list are denoted by DDL . "]
["Gender (M or F)",null,{"$f":"IFERROR((VLOOKUP(B7,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"                 If columns with DDL are mapped, the value on the source file must match the values in the drop down list.\n                 On the Census Template, File Save data validation will erase values which do not match the drop down list."]
["Hire Date  (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B8,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"  If mapping multiple times, Column B values will be stored from previous time. Column B values which do not match the Source Census Header will be highlighted in Yellow. Please update Column B values as needed for the current mapping.\nmapping."]
["COBRA Reason",null,{"$f":"IFERROR((VLOOKUP(B9,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"2) Click Copy Mapped Columns button to copy the mapped source columns systematically to Census Template tab."]
["Cobra Start Date (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B10,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""},null,"3) Click Cancel button to skip the mapping and go to Import Census Template tab to enter census."]
["E-Mail",null,{"$f":"IFERROR((VLOOKUP(B11,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Employee Status",null,{"$f":"IFERROR((VLOOKUP(B12,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Street Line 1",null,{"$f":"IFERROR((VLOOKUP(B13,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Street Line 2 \n(Can't duplicate Address 1)",null,{"$f":"IFERROR((VLOOKUP(B14,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["City",null,{"$f":"IFERROR((VLOOKUP(B15,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["State",null,{"$f":"IFERROR((VLOOKUP(B16,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Zip  Code",null,{"$f":"IFERROR((VLOOKUP(B17,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Relationship   ",null,{"$f":"IFERROR((VLOOKUP(B18,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Coverage",null,{"$f":"IFERROR((VLOOKUP(B19,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Health Coverage Tier",null,{"$f":"IFERROR((VLOOKUP(B20,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Coverage ",null,{"$f":"IFERROR((VLOOKUP(B21,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Dental Coverage Tier",null,{"$f":"IFERROR((VLOOKUP(B22,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Name Suffix",null,{"$f":"IFERROR((VLOOKUP(B23,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["PCP Number",null,{"$f":"IFERROR((VLOOKUP(B24,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medical Group Number",null,{"$f":"IFERROR((VLOOKUP(B25,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Eligible  (Y,N)",null,{"$f":"IFERROR((VLOOKUP(B26,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare  Part A  Effective Date (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B27,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Part B Effective Date (mm/dd/yyyy)",null,{"$f":"IFERROR((VLOOKUP(B28,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Status  (P,S)",null,{"$f":"IFERROR((VLOOKUP(B29,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare Reason  (AGED, DIS, ESRD)",null,{"$f":"IFERROR((VLOOKUP(B30,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Medicare HIC Number",null,{"$f":"IFERROR((VLOOKUP(B31,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Category (Usually Billing)",null,{"$f":"IFERROR((VLOOKUP(B32,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]
["Category Product - Wait Period",null,{"$f":"IFERROR((VLOOKUP(B33,DetermineSourceColumnAddr!$A$2:$B$79,2)),\"\")","v":""}]

{"sheet":"Census DDL","index":22,"n_rows":84,"n_cols":40,"rows_kept":84,"cut":false,"hidden":true,"merged":[]}
["ListYesNo","ListRelationCodeIL","ListRelationCodeSW","ListWaiveReasonCode","ListGender","ListMaritalStatusIL","ListMaritalStatusSW","ListEmployStatusIL","ListEmployStatusTX","ListEmployStatusOKNMMT","ListCobraReasonCode","ListILContReasonCode","ListHlthCoverageTypeActive","ListHlthCoverageTypeCOBRA","ListSalaryPeriod","ListMedicareElig","ListMedicareReason","ListMedicarePS","ListAddrStates","ListFullState","ListLettersOnly","ListNbrsOnly","ccSpouseRows","ccRelationEO","ListQuoteEnroll","ListQuoteRelationCode","ListQuoteCoverageType","ListMMQuoteRC","ListMMQuoteCT","ListMMQuoteCTOK","ListMMQuoteIL","ListMMEmployStatus","ListMMERelationCode","ListMMERelationCodeIL","ListMMECobraReasonIL","ListMMECobraReasonCode","ListMMEEmployStatusIL","ListMMEEmployStatusSW","ListMMECovType","ListMMEWavierReasonIL"]
["Y","Employee","Employee","Waiving - Has other coverage","F","Single","Single","Active","Active","Active","Bankruptcy/Surviving Spouse","Dependent","EO","CO","Annual","Y","Aged","Primary","IL","Illinois","~","~","Spouse","Spouse","Quoting","Employee","EO","E","EO","EO","Emp","A","SUB","SUB","AS","DV","A","A","EO","Spouse/Domestic Partner's Employer Plan"]
["N","Spouse","Spouse","Waiving - No other coverage","M","Married","Married","Retired","Retired","Retired","Chapter 11 Bankruptcy","Employee","ES","CS","Hourly","N","Disabled","Secondary","TX","Texas","!","!","Domestic Partner","Domestic Partner","Enrollment","Spouse","ES","S","ES","ES","Emp+Sp","C","SPS","SPS","DV","ET","C","C","ES","Individual Coverage (Non-Group Plan)"]
[null,"Domestic Partner","Domestic Partner",null,null,"Divorced","Divorced","COBRA","COBRA","COBRA","Divorce","Spouse","EC","CC","Monthly","U","End Stage Renal Disease",null,"OK","Oklahoma","@","@","Civil Union","Dependent Child",null,"Dependent","EC","D","EC","EC","Emp+Ch","R","DEP","DEP","ET","RT","R","R","EC","Cobra/State Continuation"]
[null,"Dependent Child","Dependent Child",null,null,"Widowed","Widowed","IL Cont","Ineligible","Ineligible","Employee Death","Spouse and Dependent","EF","CF","Semi-Monthly",null,null,null,"NM","New Mexico","#","#",null,"Disabled Dependent",null,null,"EF",null,"EF","EF","Family","T","STD","DIS","RH","BS","T",null,"EF","Medicare or other Government Program"]
[null,"Disabled Dependent","Disabled Dependent",null,null,"Domestic Partner","Domestic Partner",null,"TX Cont",null,"Employee's Medicare Entitlement",null,"DC","DC","Weekly",null,null,null,"MT","Montana","$","$",null,"Civil Union",null,null,"CO",null,"CO","CO",null,null,"DIS","DP","RT","DS","I",null,null,"Other Parent Coverage"]
[null,"Civil Union",null,null,null,"Civil Union",null,null,"TX Dependent Cont ",null,"Job Term/Discharge - SSD",null,null,null,null,null,null,null,"AL","Alabama","%","%",null,null,null,null,"CS",null,"CS","CS",null,null,"DP",null,"BN","JT","W",null,null,"Other Cost"]
[null,null,null,null,null,null,null,null,null,null,"Job Termination/Discharge",null,null,null,null,null,null,null,"AK","Alaska","^","^",null,null,null,null,"CC",null,"CC","CC",null,null,null,null,"BS","LO",null,null,null,"Other Not Specified"]
[null,null,null,null,null,null,null,null,null,null,"Layoff",null,null,null,null,null,null,null,"AZ","Arizona","&","&",null,null,null,null,"CF",null,"CF","CF",null,null,null,null,"DS","LS"]
[null,null,null,null,null,null,null,null,null,null,"Layoff - SSD",null,null,null,null,null,null,null,"AR","Arkansas","*","*",null,null,null,null,"DC",null,"DC","DC",null,null,null,null,"JT","ME"]
[null,null,null,null,null,null,null,null,null,null,"Legal Separation",null,null,null,null,null,null,null,"CA","California","(","(",null,null,null,null,"LO",null,"PT","LO",null,null,null,null,"LO","RH"]
[null,null,null,null,null,null,null,null,null,null,"Loss of Dependent Status",null,null,null,null,null,null,null,"CO","Colorado",")",")",null,null,null,null,null,null,"WP",null,null,null,null,null,"LS","SS"]
[null,null,null,null,null,null,null,null,null,null,"Loss of Student Status",null,null,null,null,null,null,null,"CT","Connecticut","_","_",null,null,null,null,null,null,"LO",null,null,null,null,null,"ME"]
[null,null,null,null,null,null,null,null,null,null,"Reduction in Hours",null,null,null,null,null,null,null,"DE","Delaware","+","+",null,null,null,null,null,null,null,null,null,null,null,null,"SJ"]
[null,null,null,null,null,null,null,null,null,null,"Reduction in Hours - SSD",null,null,null,null,null,null,null,"DC","District of Columbia","`","`",null,null,null,null,null,null,null,null,null,null,null,null,"SL"]
[null,null,null,null,null,null,null,null,null,null,"Retirement",null,null,null,null,null,null,null,"FL","Florida",1,"=",null,null,null,null,null,null,null,null,null,null,null,null,"SR"]
[null,null,null,null,null,null,null,null,null,null,"Student Comes of Age",null,null,null,null,null,null,null,"GA","Georgia",2,"[",null,null,null,null,null,null,null,null,null,null,null,null,"SS"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"HI","Hawaii",3,"]",null,null,null,null,null,null,null,null,null,null,null,null,"ST"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ID","Idaho",4,"\\"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"IN","Indiana",5,"{"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"IA","Iowa",6,"}"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"KS","Kansas",7,"|"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"KY","Kentucky",8,":"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LA","Louisiana",9,";"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ME","Maine",0,"\""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MD","Maryland","=","'"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MA","Massachusetts","[","<"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MI","Michigan","]",">"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MN","Minnesota","\\",","]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MS","Mississippi","{","."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"MO","Missouri","}","?"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NE","Nebraska","|","/"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NV","Nevada",":","a"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NH","New Hampshire",";","b"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NJ","New Jersey","\"","c"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NY","New York","'","d"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NC","North Carolina","<","e"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ND","North Dakota",">","f"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"OH","Ohio",",","g"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"OR","Oregon",".","h"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"PA","Pennsylvania","?","i"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"RI","Rhode Island","/","j"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SC","South Carolina",null,"k"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SD","South Dakota",null,"l"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"TN","Tennessee",null,"m"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"UT","Utah",null,"n"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"VT","Vermont",null,"o"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"VA","Virginia",null,"p"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"WA","Washington",null,"q"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"WV","West Virginia",null,"r"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"WI","Wisconsin",null,"s"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"WY","Wyoming",null,"t"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"u"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"v"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"w"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"x"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"z"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"A"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"B"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"C"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"D"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"E"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"F"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"G"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"H"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"I"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"J"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"K"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"L"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"M"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"O"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"P"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Q"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"R"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"S"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"T"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"U"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"V"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"W"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"X"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Y"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Z"]