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)
"ADK" <ADK[ at ]noreply2today.com> wrote in message
news:O9heMOd1GHA.1256[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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
>
>
|