I work in insurance.  I am building a template that can work with our customers who have a random number of health plan options (possibly more than one each of:  health plan, dental plan, vision plan).  The goal is to do a total plan design cost.  There are 4 options with each potential plan choice:  Employee Only (EO), Employee Plus Spouse (ESP), Employee Plus Children (ECH), Employee Plus Family (FAM).

I have a dynamic named range that concatenates all health plans, followed by all dental plans, followed by all vision plans, into a single column.  This data set can be a minimum of 3 (1 health, 1 dental, 1 vision) to a maximum of who knows (as each type of policy can have many options).

What I need is to create a dynamic table, that has the first column be the plan name (ie, health1,health2,health3,dental1,dental2,vision1,vision2,vision3, etc…) in the first column.  The second column would have EO, ESP, ECH, FAM (for the 4 options I listed earlier) for each item in the 1st column, then subsequent columns would have premium data that I can deal with.

The problem I’m having is coming up with a way to create this dynamic table where the health1,health2, etc. stuff is inserted in every 4th cell, so the 2nd column gives the 4 different options EO, ESP, ECH, FAM for each of the entries health1,health2, etc.

Anybody have a suggestion how to expand a dynamic named range so I can have it populate the first column of a dynamic length table every 4th row?

​I work in insurance.  I am building a template that can work with our customers who have a random number of health plan options (possibly more than one each of:  health plan, dental plan, vision plan).  The goal is to do a total plan design cost.  There are 4 options with each potential plan choice:  Employee Only (EO), Employee Plus Spouse (ESP), Employee Plus Children (ECH), Employee Plus Family (FAM). I have a dynamic named range that concatenates all health plans, followed by all dental plans, followed by all vision plans, into a single column.  This data set can be a minimum of 3 (1 health, 1 dental, 1 vision) to a maximum of who knows (as each type of policy can have many options). What I need is to create a dynamic table, that has the first column be the plan name (ie, health1,health2,health3,dental1,dental2,vision1,vision2,vision3, etc…) in the first column.  The second column would have EO, ESP, ECH, FAM (for the 4 options I listed earlier) for each item in the 1st column, then subsequent columns would have premium data that I can deal with. The problem I’m having is coming up with a way to create this dynamic table where the health1,health2, etc. stuff is inserted in every 4th cell, so the 2nd column gives the 4 different options EO, ESP, ECH, FAM for each of the entries health1,health2, etc. Anybody have a suggestion how to expand a dynamic named range so I can have it populate the first column of a dynamic length table every 4th row?  Read More

​