+ Reply to Thread
Results 1 to 9 of 9

Using Defined Names with Data Validation Depend and Data Validation Multi Select

  1. 03-01-2012, 02:13 PM #1
    Vinnie Chan
    Vinnie Chan is offline
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165

    Using Defined Names with Data Validation Depend and Data Validation Multi Select

    Hi,

    I want to use Data Validation in a form. I have already defined names with the columns I want to show in the Class dropdown (E9) when Category dropdown (E8) is selected. E9 also needed to be a Multi Select dropdown.

    I tried to use INDIRECT and IF...LOOKUP in Data Validation, but returned with error.

    Please help me to solve this problem. The file I'm attaching is a lot smaller than the actual one. Would that be too big to use Data Validation?

    Thanks for the help.

    Vinnie
    Attached Files Attached Files
    Last edited by NBVC; 03-01-2012 at 05:51 PM.
    Reply With QuoteRegister To Reply
  2. 03-01-2012, 02:25 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    You need to make the INDIRECT-ly looked up item the same as the actual Names... i.e. replacing all the symbols with underscore and adding an underscore in front:

    =INDIRECT("_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($E$8,";"," "),"-","_")," ","_"),"&","_"))

    if you want the resulting list not to contain the header again, reset the ranges to exclude row 3.,,, i.e for _001_ADVERTISING_AGENCIES___RELATED_SERVICES__DIRECT_MARKETING_CO , define as =Exp!$Z$4:$Z$5
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 03-01-2012, 03:06 PM #3
    Vinnie Chan
    Vinnie Chan is offline
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    Hi NBVC,

    Thanks for your help. But the formulas you provided didn't work. Got an error message - "The Source currently evaluates to an error."

    Every time I use INDIRECT, this message will show up.
    Thanks.

    Vinnie
    Reply With QuoteRegister To Reply
  4. 03-01-2012, 03:34 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    See attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-01-2012, 04:03 PM #5
    Vinnie Chan
    Vinnie Chan is offline
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    Thank you NBVC. It works perfect for the part.

    The 2nd part is Multi Select with Data Validation using the same data. I know this will need to use some VBA to do it. Would this be too much to do all this in one time?

    Thanks for your help.

    Vinnie
    Reply With QuoteRegister To Reply
  6. 03-01-2012, 04:15 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    I am not 100% sure what you are looking for but here is a google search showing samples of Multiple Selection Data Validation. Maybe those will shed some clues.
    Reply With QuoteRegister To Reply
  7. 03-01-2012, 04:17 PM #7
    Vinnie Chan
    Vinnie Chan is offline
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    Thank you for your suggestions.

    Vinnie
    Reply With QuoteRegister To Reply
  8. 03-01-2012, 04:20 PM #8
    Vinnie Chan
    Vinnie Chan is offline
    Forum Contributor
    Join Date
    05-01-2008
    Posts
    165

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    This issue is solved. But I don't know where to find the prefix "SOLVED".
    Reply With QuoteRegister To Reply
  9. 03-01-2012, 05:36 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Using Defined Names with Data Validation Depend and Data Validation Multi Select

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1