Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Data Validation using source from other worksheet (2003 SP2)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    I need to use data validation using a list maintained in another workbook on a shared network. I set up an experiment with both the list source files and the file with the references open, so of course it worked. However, for multiple users, the file will NOT be open on the network -- ideally they won't know it's there.

    I have read "Excel -- Data Validation -- Use a List from Another Workbook" at http://www.contextures.com/xlDataVal05.html and thought of two possibilities:

    1) if I have an add-in that the users are already using for its elegant macros (thank you, Loungers) and toolbars, would it be feasible to store the lists on a worksheet in the add-in? Would Excel regard that as "open" but invisible to the user?

    2) what about storing a blank hidden sheet in the user's file, having VBA load data from the network list source workbook to the hidden sheet on opening, and clearing the sheet's contents on exit (preferably also invisibly to the user)?

    Many thanks,

    Ann

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation using source from other worksheet (2003 SP2)

    1) Yes, you can refer to a defined name in a loaded add-in. (I tested it)

    2) Might be possible, but it's rather clunky and more prone to problems than 1)

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Data Validation using source from other worksheet (2003 SP2)

    Thank you! If this is a new idea, let's make a note of it for future reference ...

    L.P.H.,

    Ann

Posting Permissions

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