Results 1 to 11 of 11
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    data validation list source (Excel2000)

    I am using data validation with a 'List' source.
    I get different results depending on whether I use a named range (on another sheet), or when I directly specify the choices (separating each by commas):
    When the cell containing the data validation is 'locked' with sheet protection on,
    ..if I use the comma separated version, I get the expected 'cell is locked' Excel message when selecting from the dropdown
    ..if I use the named range as a source, a selection can be made from the dropdown even if the cell is locked and protected.

    I want to use named ranges as my source (easier maintenance) but I want the Excel 'cell is protected' message if I want to disallow selection.

    Any suggestions?

    zeddy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: data validation list source (Excel2000)

    I can replicate the problem in XL97 and it sounds like a "bug" to me.

    A "relatively" easy "workaround" is:
    when you "lock" the input cells to prevent data entry, rename the named ranges.

    If there are a lot of them, you could create them using a formula rather than a range (eg with OFFSET) and then have the an added variable that sets the rows to zero. with no rows, there is no list.
    Something like this: (change the names as appropriate)
    =OFFSET(ListSheet!$A$1,0,0,COUNTA(ListSheet!$A:$A) *ListSheet!$B$1,1)
    List starts in A1 of ListSheet and will expand and contract (automatcially) based on the entries in column A. ListSheet!B1 contains TRUE/FALSE: if true the list will be as long as the number of items in col A, if false it will be zero and the list will not work.

    B1 could be the linked cell for a checkbox if desired.

    Warning: if the sheet is not protected or the cell is not locked, and you enter false in B1 then the "validation" does not come into play and any value may be entered in the cell (manually, nothing can be entered using the dropdown)

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: data validation list source (Excel2000)

    For info the bug has been fixed in XL 2003 and you get the error message for comma separated lists and named ranges.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: data validation list source (Excel2000)

    Hi Tony

    ..so when my Users finally upgrade in 2008 it will be fixed!

    ..I guess one solution would be to 'delete' the validation when the sheet is 'protected', and to use event trapping to 're-introduce' the required data validation when the sheet is
    'unlocked'.

    Trouble is I need to do this on a 'per record' i.e. row basis.
    I use event trapping to set certain allowable conditions.
    For example, I have two sections of columns for data entry.
    You can't input data into section 2 columns on a record row unless section 1 columnson the row are completed first.
    Section 2 columns contain cells with data validation dropdown lists.
    These are the ones I want to give a 'cell protected message' if the cells are 'locked'.

    zeddy

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: data validation list source (Excel2000)

    Instead of using data validation, have you thought of using actual comboboxes?

    The ones from the control toolbox have much more control and you can enable and disable them easily using code.

    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: data validation list source (Excel2000)

    Hi Steve,

    I like the idea but I need to allow the dropdown entries in certain data record rows but not in others.
    I have used locked cells to distinguish which.

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: data validation list source (Excel2000)

    Hi Steve,

    ..I shall look at comboboxes as suggested.
    Hmmm.The beauty of Excel is there are so many ways to skin the cat.
    (apologies to any pet-loving non Brits - re 'there are so many way to skin a cat')

    zeddy

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: data validation list source (Excel2000)

    You could use something like this:
    =OFFSET(ListSheet!$A$1,0,0,COUNTA(ListSheet!$A:$A) *(ROW()=ListSheet!$B$1),1)

    If you put 5 in ListSheet!B1 (eg) the named range will only have a size if the active row = 5. In all other rows the pulldown will be disabled. You could use an OR if you want multiple rows enabled. Perhaps even a MATCH function in a "list of rows"

    Perhaps even another named range that just evaluates whether the active row is "enabled".

    Plenty of possibilities to test if desired.

    The warning still goes though, disable the list and any value may be eneterd in the validation cell, so it must be protected.

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation list source (Excel2000)

    Out of curiosity (which killed the cat, by the way), why is it that one would go around skinning cats in the first place, much less need more than one way of doing so? <img src=/S/catty.gif border=0 alt=catty width=19 height=18> I've never understood that expression.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: data validation list source (Excel2000)

    I always heard that is was a southern expression and more completely is:
    "more ways to skin a catfish"
    but was abbreviated since catfish are called cat.

    Steve

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data validation list source (Excel2000)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •