+ Reply to Thread
Results 1 to 3 of 3

Need to use Ampersand sign

  1. #1
    Registered User
    Join Date
    05-20-2011
    Location
    Milwaukee, WI
    MS-Off Ver
    Excel 2007
    Posts
    6

    Need to use Ampersand sign

    3 days now on this ... I give up! Someone Please help me with this ... (thanks)

    I am trying to create a Dynamic Drop Down List with Data Validation. My only problem is that I MUST use the ampersand sign in my lists that Im creating names for! Example:

    In my Dept List I have for example:
    Vitamins & Minerals
    Herbs & Plants
    Supplements
    Food & Beverage

    As long as my list has only one word, Im fine. However, when there is an Ampersand in it, my link breaks for my look up! I believe the problem is in part the spaces on either side of the ampersand as well.

    for the cell next to it that will pull the info from lets say Supplements, I use a string a string =INDIRECT($U$2) which references whatever is in the cell that Im trying to pull the info from .. Can anyone help me please....

    In plain English .. If the dept says Vitamins, it should pull up a list that has all my Vitamins in it .. if it had supplements, it should pull the list that has all my supplements.
    If it was going to pull a list from lets say Food and Beverage, then it would need to go through a few steps to get down to the chips, or candy bars, or whatever ...

    I dunno .. maybe the Indirect is the wrong approach for this ?
    Thanks!
    PS ... I got the idea from :
    http://www.youtube.com/watch?v=Los7N...A5A63658E23B0D

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Need to use Ampersand sign

    If you're using dependent drop down boxes, you can't have spaces (and possibly ampersands) in the name of the dependent range. You therefore need to convert the name shown in the drop down list to something acceptable for the named range ... like substitute underscore for spaces and ampersands.

    If you upload a sample workbook with some typical data it will be easie to present a solution.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Need to use Ampersand sign

    read here
    http://www.contextures.com/xldataval02.html#TwoWord
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1