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


  • 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:

    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


    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


  • 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)
    =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!