Excel 2007 : List with dynamic source issue

Ok, I've got 2 named ranges: ActivityCodes (a list of activities) and PlanCodes (built by the user from the ActivityCodes, so it is essentially a subset of ActivityCodes). Now, I've got a list item where I want the source to be the PlanCodes if anything has been selected, otherwise use the ActivityCodes as the source. I have it working somewhat using this formula in the source field in the data validation area for the list:
=IF(COUNTA(PlanCodes)>0,PlanCodes,ActivityCodes).

The problem is that when I pull down the list when PlanCodes is the source, the selected item is the BOTTOM of the list, so it looks blank unless I scroll UP to see the data. Does anyone know of a way to DE-select anything from the list so that when the list is pulled down you see the list from the top?

Hi kentwistle,

Welcome to the forum.

Not sure but there may be some freezing issue or something, would suggest you to post the sample file. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Change the defined name formula to

=Sheet1!$I$2:INDEX(Sheet1!$I:$I,COUNTA(Sheet1!$I:$I))

My pleasure. Please do not forget to mark the thread Solved.