My First post - I can be long-winded, so I will be as precise as I can. I work within the health insurance industry.
I am attempting to make requests by our Custer Resolution Team to fix Health Insurance Enrollments that were incorrectly handled
by our call center, thereby interrupting premium rates, incorrect termination dates, removal of individuals from the application. These
actions have ripple effects all the way through from the consumer to the insurance company and Medical Providers. These requests must
be done manually by the Insurance Carrier, due to the fact that other compute codes prevent sending new data. In order to get the systems
synched, they tediously fill out a spreadsheet form and submit that request to the insurance carrier.
So here is what I have to work with - at home I use Excell 2013. I have taken on this project as a self-learning experience, to later
submit to the legal department and to the appropriate Supervisors and Managers of the CRT team to use. The office itself uses fully licensed
Excell 365 ( so I am hoping that this is not a problem).
So let me start by telling you what I have.
I have a LARGE piece of data that consists of 8 columns and 3672 rows of data
The Data looks like this
Insurance Carrier Level of Plan Plan ID Number Full Plan Name Rating Area County Age Premium
XXXXX YYYY 12345XX1234567 "NAME " Rating Area 1 XXXX 0-14 178.40
XXXXX YYYY 12345XX1234567 "NAME " Rating Area 1 XXXX 15 194.26
XXXXX YYYY 12345XX1234567 "NAME " Rating Area 1 XXXX 16 200.32
The example above is just ONE plan that continues all the way up to the age of 64 - The Cycle Repeats with the next plan and the next and the next
there are 9 Plans in total each plan has eight rating areas
Here is my Goal ----
the CRT chooses the Level of Plan --- the Next Selection box would only reflect the PLAN names within that LEVEL
So for instance, if a drop-down selection box done via a data validation selection were to say choose a Level 4 Plan then the Plan Name selection box would ONLY display those plans within LEVEL 4
once that was done...then based on the age of the individual ( which is calculated as of the plan year 01/01/xx ) would automatically fill in the correct total premium into a specific cell and the plan number
into a specific cell
I have included screenshots
1: I am unsure if the large data should be a text format or a table format
2: I have attempted to watch and understand VLOOKUP, OFFSET, MATCH, and INDEX but I am not understanding
3: Is what I am seeking to do possible?
4: And if you can at least point me in the right direction as to a youtube video or other reading source to help me fully understand would be greatly appreciated.
I am not an individual that wants someone to do it for me I am willing to put in the time and effort to learn as well. I am just stuck
Also, note I felt that at least bare minimum the actual correction sheet should be a single simple form with other data for dropdown selection boxes
be placed on several other sheets to keep things simple and for easy updating as time passes and new information may be needed for addition especially
if previous years need to be added.
The Subscriber Information ( Green Boxes ) are located in Columns D and F running from cells 6 through 13
The way the enrollment needs to be fixed by the Insurance carrier ( Blue Boxes) are in Columns D through Y and cells 21 through 25
the cell that I would like to have the RATE automatically put into based on selection s and AGE are in cells L21 L25
I hope I was clear enough...
I am attempting to make requests by our Custer Resolution Team to fix Health Insurance Enrollments that were incorrectly handled
by our call center, thereby interrupting premium rates, incorrect termination dates, removal of individuals from the application. These
actions have ripple effects all the way through from the consumer to the insurance company and Medical Providers. These requests must
be done manually by the Insurance Carrier, due to the fact that other compute codes prevent sending new data. In order to get the systems
synched, they tediously fill out a spreadsheet form and submit that request to the insurance carrier.
So here is what I have to work with - at home I use Excell 2013. I have taken on this project as a self-learning experience, to later
submit to the legal department and to the appropriate Supervisors and Managers of the CRT team to use. The office itself uses fully licensed
Excell 365 ( so I am hoping that this is not a problem).
So let me start by telling you what I have.
I have a LARGE piece of data that consists of 8 columns and 3672 rows of data
The Data looks like this
Insurance Carrier Level of Plan Plan ID Number Full Plan Name Rating Area County Age Premium
XXXXX YYYY 12345XX1234567 "NAME " Rating Area 1 XXXX 0-14 178.40
XXXXX YYYY 12345XX1234567 "NAME " Rating Area 1 XXXX 15 194.26
XXXXX YYYY 12345XX1234567 "NAME " Rating Area 1 XXXX 16 200.32
The example above is just ONE plan that continues all the way up to the age of 64 - The Cycle Repeats with the next plan and the next and the next
there are 9 Plans in total each plan has eight rating areas
Here is my Goal ----
the CRT chooses the Level of Plan --- the Next Selection box would only reflect the PLAN names within that LEVEL
So for instance, if a drop-down selection box done via a data validation selection were to say choose a Level 4 Plan then the Plan Name selection box would ONLY display those plans within LEVEL 4
once that was done...then based on the age of the individual ( which is calculated as of the plan year 01/01/xx ) would automatically fill in the correct total premium into a specific cell and the plan number
into a specific cell
I have included screenshots
1: I am unsure if the large data should be a text format or a table format
2: I have attempted to watch and understand VLOOKUP, OFFSET, MATCH, and INDEX but I am not understanding
3: Is what I am seeking to do possible?
4: And if you can at least point me in the right direction as to a youtube video or other reading source to help me fully understand would be greatly appreciated.
I am not an individual that wants someone to do it for me I am willing to put in the time and effort to learn as well. I am just stuck
Also, note I felt that at least bare minimum the actual correction sheet should be a single simple form with other data for dropdown selection boxes
be placed on several other sheets to keep things simple and for easy updating as time passes and new information may be needed for addition especially
if previous years need to be added.
The Subscriber Information ( Green Boxes ) are located in Columns D and F running from cells 6 through 13
The way the enrollment needs to be fixed by the Insurance carrier ( Blue Boxes) are in Columns D through Y and cells 21 through 25
the cell that I would like to have the RATE automatically put into based on selection s and AGE are in cells L21 L25
I hope I was clear enough...
