General Guidance on - Large Data bases 2 years worth and Validation Drop Down Selection

[thhynes]

So I would like to start by saying that I need help, however, I am willing to do the reading and training myself - I just need to be pointed in the right direction.
perhaps as a beginner, I have bitten off more than I can chew as the old saying goes.

Let me tackle the Data first. There are currently 2 plan years worth of Data. Each year is currently separate from each other. Meaning. one excels sheet was created for 2020 and a New one for 2021. There will be subsequent additions for 2022, 2023, 2024, 2025, and so on. However There only needs to be two years ACTIVE at any given time, this is because once the PLAN year ends on 12/31/20XX, typically corrections to the previous year are DENIED. Therefore I would only need two years at any given time, the current year and the previous year.

Each Year has the following information

Company Name Metal Level Plan ID Number Plan Name Rating Area Rating Area Name Age Individual Rate

Currently, this datasheet contains 8 Columns and 3,673 Rows

There are four Metal levels to choose from.
Some of those Levels have up to four Different Plan names, Some only have ONE
Each Plan Name is assigned a separate Plan ID Number. This number remains constant regardless of the rating area and age of the individual
Rating Area is the county in which the individual lives
The age defines what a premium is for a person that is of that age
However, the Premium is also dependent upon the individual's age as of January 1 of the Plan Year

My Goal is this - To create a Data Sheet that our Resolution Team can both input some data such as SSN, DOB, Subscriber Numbers, Enrollment Numbers

through Validation Drop Down Boxes

A Plan Year is chosen either 2020 or 2021
A Metal Level plan is chosen - Catastrophic, Bronze, Silver, Gold
As a result of the two choices, the Plan Names are only shown for that Metal Level and that Plan Year within the drop-down box
A Rating Area is Selected
A Date of Birth is typed into a cell
... from this point, I would like a designated two CELLS to autofill with
A: The Plan Number assigned into a specific Cell right now that would be Cell 15
B: The Plan Rate I29 for the Primary, I30 for the Spouse, I31-36 for Each Dependent Child

Because the Data as of now is in TEXT, with HEADERS for DATA SEARCH, not a TABLE should I convert it to a TABLE?

I am guessing that with the SIZE of the data, that it would be BEST to keep them separate and not create one large datafile.

Thank you.

[Richard Buttrey]

Hi,

As I understand your request, your last comment is wrong.

All your data, and I mean all years and all plans is easily held on a single sheet database. Having a single sheet database is always the most efficient way to proceed.

I'm assuming all your worksheets have the same column layout and all exist in a single workbook. If that's the case then a one of task using a simple macro to consolidate them all would be the first thing to do. If you want help with that then upload a representative example of your workbook, we don't necessarily need every single row from every sheet.

[thhynes]

Thanks for the assistance, much appreciated

Yes, you are correct both 2020 and 2021 are on two different sheets

The Data looks like the file attached.
The biggest difference between the years 2020 and 2021 is that the Premiums are Different and Plan ID numbers are Different and Plan names are different
the rating area and the way the ages are listed within this example are the same.Example Data Sheet.jpg