Validation issue

[ADK]

I have set up my cells on one sheet with validation to a list. The list is
on a different sheet. I accomplished this be selecting the 20 possible cells
to contain data in the list and Insert|Name called MyList. Now on the other
sheet, for the cell validation the source is: =MyList

The issue is not all 20 cells will hold content at all times (depends on how
many activities the user needs to define). When a user clicks the pull-down
to choose from the list, the pull-down starts at the first blank cell ....so
you have to scroll up on the pull-down list to see any content.

I know it works fine when the source is from the same sheet and you just
select the range.

Is there a way to force the pull-down list to start at the first cell?
.....or is a limitation to use this method of gathering validation data from
a different sheet?

I am using Excel 2000

-ADK

 

[Bob Phillips]

It works exactly the same on the same sheet as far as I can see.

If the blanks are only at the start of the range, you could try formula
like so in the name

=OFFSET(Sheet3!$H$1,COUNTBLANK(Sheet3!$H$1:$H$10),0,COUNT(Sheet3!$H:$H),1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)