In Excel 2007, I have implemented an ActiveX combobox whose named range contents are dependent on the value in another cell (someone's age, stored in B5). Basically I wish to load one of two lists (an aduly list or youth list) whose length is set by the standard OFFSET/COUNTA method. One list is much longer than the others. The named range is setup under Name Manager as follows:

=IF($B$5<18,OFFSET(YouthList!$B$3,0,0,COUNTA(YouthList!$B:$B),1),OFFSET(AdultList!$B$3,0,0,COUNTA(AdultList!$B:$B),1))

Excel seems to be fixing the length of the list based on the value in B5 on opening the spreadsheet. If the value is changed then the correct list is displayed but the number of entries displayed is that of the list on opening. For example,if the YouthList has 20 entires and the AdultList has 100 entries and the spreadsheet is opened with the Age set at 12, then the YouthList is correcte displayed in the combobox. However if I update the value of age to 50, the combobox shows the AdultList but only shows the first 20 entries.

If the reverse happens and I open the workbook with age at 50 and then update it to 12, then the YouthList displays but it has a huge number of blank lines at the bottom showing 100 entries.

What is going on and how can I correct it. Note that I need autocomplete and so am using comboboxed. The functionality worked fine under Data Validation.