Dynamic Validation List

  • I have a sheet ( database ) which has dymamic validation lists based on a another sheet called "look ups"


    The problem I have is
    When I choose any of the validation drop downs there are two blank entries at the bottom and the cursor is on the first one of these.
    I would like the cursor to be on the first entry in the validation list


    Can anyone help


    Steve

  • Re: Dynamic Validation List


    After creation of your dynamic validation list, have the cell that references it point to the first entry on the list:

    Code
    Sheets(2).Range("A2").Value = Sheets(1).Range("A2").Value


  • Re: Dynamic Validation List


    Is it pointing to the first blank entry because the entry cell is currently blank? If so, do you need the blanks included in your validation list? If not, eliminate them (I'm assuming you are using OFFSET to create the lists) by decreasing the 5th argument by 2.

  • Re: Dynamic Validation List


    Thomach


    Yes, usually the cell will be blank prior to choosing from the validation list


    The refers to formula I am using is


    =Offset('Look up tables'!$G$5,0,0,CountA('Look up tables'!$G:$G,1))


    The headings for each look up is in row 4



    Steve

  • Re: Dynamic Validation List



    It will count the heading and any other nonblank cell in that entire column then offset that number from G5 down. Eliminate all other eroneous data from your column then change your formula to account for your heading and anyother information in that column.



    =Offset('Look up tables'!$G$5,0,0,CountA('Look up tables'!$G:$G,1)-1)
    or
    =Offset('Look up tables'!$G$5,0,0,CountA('Look up tables'!$G:$G,1)-2)

  • Re: Dynamic Validation List


    Are you sure your formula isn't


    =Offset('Look up tables'!$G$5,0,0,CountA('Look up tables'!$G:$G),1)


    Note the change in the ")" location at the end.


    if so, try using:


    =Offset('Look up tables'!$G$5,0,0,CountA('Look up tables'!$G:$G)-2,1)


    OR, if the first row is a header that should not be part of the drop down, use:


    =Offset('Look up tables'!$G$5,1,0,CountA('Look up tables'!$G:$G)-1,1)


    EDIT: You're too quick, ajdjad! : D

  • Re: Dynamic Validation List



    Another formula you might experiment with is.


    =Offset('Look up tables'!$G$5,0,0,Match("*",'Look up tables'!$G:$G,-1)-1,1)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!