Using one drop down list to generate another

M

MJBVLC

New Member
Joined
Feb 9, 2009
Messages
11
Hi,

I am really struggling to find a way of generating one drop down list (ie data validation) that depends on the value selected in another. eg for the data below, I would like one cell to have a drop down list to select the meal (breakfast, lunch or dinner) and then another cell to have a drop down list to select the meal option (ie salad, sandwich or pizza if lunch has been selected). I haven't spent any time working out how I get a unique list for the first drop down list as there was no point until I got the 2nd list sorted.

I need to do this without using VBA, as neither I nor the eventual user is trained in this area. I have been trying to use array formulae but without success as I generate lists of the form {FALSE,FALSE,FALSE,"Salad", "Sandwich", "Pizza",FALSE,FALSE,FALSE}.

Can anyone help - PLEASE!

<table style="border-collapse: collapse; width: 128pt;" width="170" border="0" cellpadding="0" cellspacing="0"><col style="width: 80pt;" width="106"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl80" style="height: 15pt; width: 80pt;" width="106" height="20">Meal</td> <td class="xl80" style="width: 48pt;" width="64">Options</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Breakfast</td> <td class="xl80">eggs</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Breakfast</td> <td class="xl80">toast</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Breakfast</td> <td class="xl80">fruit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Breakfast</td> <td class="xl80">cereal</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Breakfast</td> <td class="xl80">coffee</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Lunch</td> <td class="xl80">salad</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Lunch</td> <td class="xl80">sandwich</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl80" style="height: 13.5pt;" height="18">Lunch</td> <td class="xl80">pizza</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Dinner</td> <td class="xl80">roast</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Dinner</td> <td class="xl80">stir fry</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl80" style="height: 12.75pt;" height="17">Dinner</td> <td class="xl80">curry</td> </tr> </tbody></table>
<table style="border-collapse: collapse; width: 96pt;" width="128" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl78" style="height: 15pt; width: 48pt;" width="64" height="20">
</td> <td class="xl78" style="width: 48pt;" width="64">

</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl78" style="height: 13.5pt;" height="18">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl78" style="height: 12.75pt;" height="17">
</td> <td class="xl78">
</td> </tr> </tbody></table>
 
Sort by date Sort by votes
Thanks for that link, sanrv1f. Some very interesting & useful stuff there. However, my data is in one table and splitting it into separate columns isn't an option (much larger than in the example I gave).

I'm still thinking array formulae might be the answer......

MJB
 
Upvote 0
Here is my actual table, which cannot be re-ordered..... I'd like to avoid having to name cell ranges - there's an awful lot! Thanks

<table style="border-collapse: collapse; width: 110pt;" width="146" border="0" cellpadding="0" cellspacing="0"><col style="width: 54pt;" width="72"> <col style="width: 56pt;" width="74"> <tbody><tr style="height: 11.25pt;" height="15"> <td class="xl1064" style="height: 11.25pt; width: 54pt;" width="72" height="15">30.0</td> <td class="xl1064" style="border-left: medium none; width: 56pt;" width="74">8.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">30.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">9.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1066" style="border-top: medium none; height: 11.25pt;" height="15">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">10.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">30.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">11.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1066" style="border-top: medium none; height: 11.25pt;" height="15">30.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">12.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">30.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">13.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1066" style="border-top: medium none; height: 11.25pt;" height="15">30.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">14.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">30.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">15.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">11.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">12.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">13.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">14.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">15.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">16.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">17.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">18.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">19.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">20.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">21.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">13.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">14.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">15.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">16.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">17.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">18.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">19.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">20.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">21.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">22.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">23.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">24.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">25.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">26.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">27.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">28.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">29.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">31.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1078" style="border-top: medium none; height: 11.25pt;" height="15">65.0</td> <td class="xl1068" style="border-top: medium none; border-left: medium none;">25.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1078" style="border-top: medium none; height: 11.25pt;" height="15">65.0</td> <td class="xl1068" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">20.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">24.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">27.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">80.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">25.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">25.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">35.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">40.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">45.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">25.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">35.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">40.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">45.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">25.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">35.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">40.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">45.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">35.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">40.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">45.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">50.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">40.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">50.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">60.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">35.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">45.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">55.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1067" style="border-top: medium none; border-left: medium none;">35.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">40.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">45.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">50.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">55.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">60.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">65.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">70.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">10.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">11.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">12.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">13.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">14.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">15.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">16.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">17.5</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">14.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">15.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">16.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">17.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">18.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">19.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">20.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">21.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">22.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">23.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">24.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">17.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">18.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">19.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">20.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">21.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">22.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">23.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">24.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">25.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">26.1</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">27.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">28.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">29.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">31.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">32.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">33.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">34.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">35.0</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1078" style="border-top: medium none; height: 11.25pt;" height="15">65.0</td> <td class="xl1070" style="border-top: medium none; border-left: medium none;">29.9</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1078" style="border-top: medium none; height: 11.25pt;" height="15">65.0</td> <td class="xl1070" style="border-top: medium none; border-left: medium none;">34.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">25.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">29.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">32.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">35.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">80.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">31.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">36.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">41.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">46.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">51.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">31.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">36.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">41.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">46.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">51.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">31.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">36.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">41.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">46.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">51.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">37.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">42.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">47.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">52.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">57.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">37.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">47.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">57.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">67.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">42.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">52.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">62.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">42.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">47.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">52.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">57.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">62.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">67.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">72.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">77.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">12.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">13.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">14.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">15.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">16.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1066" style="border-top: medium none; border-left: medium none;">17.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1066" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">18.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">30.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">19.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">17.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">18.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">19.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">20.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">21.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">22.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">23.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">24.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">25.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">26.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">45.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">27.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">20.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">21.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">22.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">23.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">24.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">25.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">26.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">27.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">28.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">29.8</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">31.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">32.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">33.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">34.8</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">35.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">36.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">37.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">60.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">38.7</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1078" style="border-top: medium none; height: 11.25pt;" height="15">65.0</td> <td class="xl1070" style="border-top: medium none; border-left: medium none;">33.9</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1078" style="border-top: medium none; height: 11.25pt;" height="15">65.0</td> <td class="xl1070" style="border-top: medium none; border-left: medium none;">38.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">30.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">34.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">37.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">75.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">40.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">80.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">36.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">36.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">41.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">46.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">51.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">85.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">56.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">37.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">42.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">47.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">52.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">90.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">57.2</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">38.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">43.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">48.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">53.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">95.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">58.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">44.1</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">49.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">54.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">59.0</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">100.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">63.9</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">44.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">54.7</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">64.6</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1069" style="border-top: medium none; height: 10.5pt;" height="14">110.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">74.6</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">50.4</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">60.3</td> </tr> <tr style="height: 10.5pt;" height="14"> <td class="xl1067" style="border-top: medium none; height: 10.5pt;" height="14">115.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">70.3</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">50.8</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">55.8</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">60.8</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">65.8</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">70.7</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">75.7</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">80.7</td> </tr> <tr style="height: 11.25pt;" height="15"> <td class="xl1067" style="border-top: medium none; height: 11.25pt;" height="15">120.0</td> <td class="xl1069" style="border-top: medium none; border-left: medium none;">85.6</td> </tr> </tbody></table>
 
Upvote 0
one option is to sort the data based on column 1 and use the below formula in data validation
Excel Workbook
ABCDE
1308
2309308
33010
43011
53012
63013
73014
83015
93010.5
103011.5
113012.5
123013.5
133014.5
143015.5
153016.5
163017.5
173012.3
183013.2
193014.2
203015.2
213016.2
223017.2
233018.2
243019.2
254511
264512
274513
284514
294515
304516
314517
324518
334519
344520
354521
364514.3
374515.3
384516.3
394517.3
404518.3
414519.3
424520.3
434521.3
444522.3
454523.3
464524.3
474517
484518
494519
504520
514521
524522
534523
544524
554525
564526
574527
586013
596014
606015
616016
626017
636018
646019
656020
666021
676022
686023
696024
706025
716026
726027
736028
746029
756030
766031
776017.1
786018.1
796019.1
806020.1
816021.1
826022.1
836023.1
846024.1
856025.1
866026.1
876027.1
886028
896029
906030
916031
926032
936033
946034
956035
966020.8
976021.8
986022.8
996023.8
1006024.8
1016025.8
1026026.8
1036027.8
1046028.8
1056029.8
1066030.8
1076031.8
1086032.8
1096033.8
1106034.8
1116035.7
1126036.7
1136037.7
1146038.7
1156525
1166530
1176529.9
1186534.9
1196533.9
1206538.9
1217520
1227524
1237527
1247530
1257525.4
1267529.4
1277532.4
1287535.4
1297530.1
1307534
1317537
1327540
1338025
1348030.7
1358036.2
1368525
1378530
1388535
1398540
1408545
1418531
1428536
1438541
1448546
1458551
1468536.2
1478541.1
1488546.1
1498551.1
1508556
1519025
1529030
1539035
1549040
1559045
1569031.4
1579036.3
1589041.3
1599046.3
1609051.3
1619037.3
1629042.3
1639047.2
1649052.2
1659057.2
1669525
1679530
1689535
1699540
1709545
1719531.8
1729536.8
1739541.8
1749546.7
1759551.7
1769538.4
1779543.4
1789548.4
1799553.3
1809558.3
18110030
18210035
18310040
18410045
18510050
18610037.1
18710042.1
18810047.1
18910052.1
19010057
19110044.1
19210049
19310054
19410059
19510063.9
19611030
19711040
19811050
19911060
20011037.4
20111047.4
20211057.4
20311067.3
20411044.7
20511054.7
20611064.6
20711074.6
20811535
20911545
21011555
21111542.7
21211552.7
21311562.7
21411550.4
21511560.3
21611570.3
21712035
21812040
21912045
22012050
22112055
22212060
22312065
22412070
22512042.9
22612047.9
22712052.9
22812057.9
22912062.9
23012067.9
23112072.9
23212077.9
23312050.8
23412055.8
23512060.8
23612065.8
23712070.7
23812075.7
23912080.7
24012085.6
Sheet2


Code: 
E2 -> Data validation
 
=OFFSET($B$1,MATCH($D$2,$A$1:$A$240,0)-1,,COUNTIF($A$1:$A$240,$D$2))
 
Upvote 0
another option using a helper column and array formula

first, using and array formula, list all the entries correspoding to the selected value,
Excel Workbook
IJKLM
130830
23098
330109
4301110
5301211
6301312
7301413
8301514
9451115
10451210.5
11451311.5
12451412.5
13451513.5
14451614.5
15451715.5
16451816.5
17451917.5
184520
194521
206013
216014
226015
236016
246017
256018
266019
276020
286021
296022
306023
316024
326025
336026
346027
356028
366029
376030
386031
396525
406530
417520
427524
437527
447530
458025
468525
478530
488535
498540
503010.5
513011.5
523012.5
533013.5
543014.5
553015.5
563016.5
573017.5
Sheet2
#VALUE!


then use this formula in data validation

Code: 
=OFFSET($M$1,1,,SUMPRODUCT(--($M$2:$M$41<>"")))
 
Upvote 0
Your last solution has done the trick. Shame that it can't all be done within Data Validation, but the boss is happy, so all's well........

Thanks for your help - all your suggestions have added to my Excel knowledge.

MJB
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
890
smozgur
smozgur
A
  • Solved
How to create a drop down list validation based on cell value
2
Replies
10
Views
580
Akuini
Akuini
J
  • Solved
how to lookup one value to set a range for another lookup
2
Replies
14
Views
636
KRice
K
M
  • Solved
Data Validation Drop-Down List Reference in Another Sheet
Replies
8
Views
643
miffy45
M
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top