How To Use Sample Retiree File Spreadsheet - Updated 01/31/08
A Sample Retiree File Spreadsheet was created for Plan Sponsors uploading retiree files to the RDS Secure Web Site. To use this sample spreadsheet, follow the steps below. Refer to How To Upload a File for detailed information about the retiree record layout. Before submitting your retiree file, please carefully review each field for accuracy.
This spreadsheet is intended for manual entry only. This spreadsheet is not intended to support cut and paste functions and does not allow you to enter data from other applications.
- Select the Sample Retiree File Spreadsheet. The file will open automatically in Excel.
- If prompted, enable macros. The following spreadsheet displays:
- The spreadsheet contains the following columns: Application Number, Covered Individual's SSN, Covered Individual's HICN, Covered Individual's First Name, Covered Individual's Middle Initial, Covered Individual's Last Name, Covered Individual's Birth Date, Covered Individual's Gender, Covered Individual's Effective Date, Covered Individual's Termination Date, Unique Benefit Option Identifier, Covered Individual's Relationship to Retiree, and Transaction Type.
- Click on the Save for Transmit button in column A of the spreadsheet.
Note: If you get an error message about macros being disabled, you will need to enable them before proceeding. To enable macros, from the Tools menu, select Macros and then Security. Select the Medium radio button and click Ok. Close Excel. Open the spreadsheet again and select Enable Macros. Repeat step 4.
- Do a Save As and name the file applicable to the retiree file you are working with, for example: Application ID and date of the file. This file name will be used to upload and download your retiree list using the RDS Secure Web Site Upload Feature.
Note: The RDS Center recommends that you save your retiree file on a local drive (e.g. c:\) and not on your network. Uploading files from the network can be slow and may cause the RDS Secure Web Site to timeout. In addition, if your file takes more than 5 minutes to upload, stop/cancel the upload process, and try again.
- Click on the Save button. Excel will save your file twice: first with a file extension of .xls for the Excel program, and secondly with a file extension of .csv for a "comma delimited value file" format.
Note: Depending upon the version of Excel you are using, you may get a second Excel screen for naming and saving your file but it will have the .csv extension. If you get this, simply click on Save again and Yes when it tells you the file already exists and asks if you want to save it again.
- Begin entering data in the spreadsheet. Please note the following important facts about the spreadsheet and data entry process:
- Fields with an asterisk (*) are required.
- Values for Gender and Relationship are shown in the column heading or you can pick from the dropdown box for the cell.
- When typing a date in any of the date columns (Covered Individual's Birth Date, Covered Individual's Effective Date, or Covered Individual's Termination Date) you must enter either a slash (/) or a dash (-) in between the month, day, and year regardless of the date format you entered. For example: if you are trying to enter a date of January 1, 2006 you can enter 01/01/06, 01-01-06, 2006/01/01, or 01-01-2006. Any valid Excel date format will generate the required CCYYMMDD date of 20060101 using our macro-you cannot simply key in the format of 20060101. Our macro converts it to the 20060101 format; it cannot be keyed manually. If you see a # sign in any date column it means the data is not valid. Try retyping the date in one of the above formats. DO NOT send a file with # signs to CMS' RDS Center.
- Termination Date is a special case when using the file upload process. In cases of on-going coverage you should enter the plan year-end-date in this field. You should only enter a date other than the plan year-end-date if you are specifically aware of another coverage end date. You are required to complete this field (do not leave it blank).
- When you have completed entering the data in the spreadsheet, please carefully review each field to ensure the data is in the proper field format, etc. Refer to How To Upload a File for detailed information about the retiree record layout. After you have carefully reviewed your retiree file, click on the Save for Transmit button again. Do not use Save As function. The Save for Transmit button runs special macros to format the Retiree File.
- Click on the Save button.
- When prompted with the File already exists message, click Yes to replace existing file.
- Repeat step 10 for the .csv file.
- When using Upload Retiree Files on the RDS Secure Web Site, be sure to select the .csv file. CMS' RDS Center cannot process Excel (.xls) files.
