Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation (Pick from list) (2000 SR1)

    Hi all,

    In the help file, it states that data validation, pick from list, cannot be set to a range in another worksheet. Therefore, I have set links to cells in a master sheet, where the user can change the items in the "list" range and the changes will be reflected in the sheet with the data validation.

    In the sheet with the validation list range, any link to a blank cell in the master sheet returns a "0". Is there any way to keep this cell "empty" where the pick from list dropdown will not show the "0"?

    I have tried the custom format "0;-0;;@" which will hide the zero values, but the drop down list includes blank choices after I apply the format. Any ideas?

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    By the way, links from the range with the dropdown list data are to a hidden sheet, if that makes any difference.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    What happens if you use a formula like this:

    <pre>=IF(Sheet2!A1="","",Sheet2!A1)
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    Legare,

    I tried that, but the formula won't take....it keeps listing the text of the formula in the cell instead of the result. Tried formatting the cells to various things such as general, number, and others but to no avail.

    Wait...the cells in the "master sheet" are locked. Let me try unlocking them and see if that makes a difference. BRB...

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    Unlocking the cells in the "master sheet", Sheet!2, in your suggestion did the trick. Cells in the sheet with the validation now show "" instead of zero.

    I do wonder if there is any way that the validation drop down can be altered so that it does not include the blank cells. The "ignore blank" option seems only to prevent the error message when a blank cell is selected.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    The cells in the master sheet being locked should not make any difference.

    If the formula is displaying, then the cell must have been formatted as text when the formula was entered. Just changing the format to general will not change what is in the cell from text to a formula. You must change the cell format to general, then select the cell, press F2, then press enter. That should change the text into a formula.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    No way that I know of.
    Legare Coleman

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    Thanks Legare,

    I think I will play around with some VBA to see if I can get the validation range to change according to whether the cell contents of the master sheet range = "".

    Thanks,

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Pick from list) (2000 SR1)

    I believe that the Formula1 property of the Range Validation object changes specifies the List range. If you use VBA to create a list with no empty cells, then you should be able to change the Formula1 property to specify that range.
    Legare Coleman

Posting Permissions

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