Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Using a Named table in Data Validation

  1. #1
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using a Named table in Data Validation

    Hi all, hope you can help
    I have created a named table called Customer_List
    I select List in data validation
    But when I type =Customer_List into the source box I get the error
    “The formula you typed contains an error”
    But the named table has dotted lines around it so it looks like its selected it.
    Can anybody offer any advice please
    praemonitus, praemunitus

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Using a Named table in Data Validation

    What is the reference of Customer_List, i.e. where does it point to?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using a Named table in Data Validation

    Thanks for getting back to me Aladin
    The list at present extends from “AY3” to “AY9” with the header in “AY3”, but this list will grow as I enter more names
    praemonitus, praemunitus

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Using a Named table in Data Validation

    Quote Originally Posted by Bagsy View Post
    Thanks for getting back to me Aladin
    The list at present extends from “AY3” to “AY9” with the header in “AY3”, but this list will grow as I enter more names
    What is the name of the sheet housing this list?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using a Named table in Data Validation

    The Sheet is called "Lists"
    praemonitus, praemunitus

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Using a Named table in Data Validation

    Quote Originally Posted by Bagsy View Post
    The Sheet is called "Lists"
    Define CusList in the Name Manager as referring to:

    =Lists!$AY$2:INDEX(Lists!$AY:$AY,MATCH(REPT("z",255),Lists!$AY:$AY))

    Now you should be able to use CusList in Source of Data Validation as List.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using a Named table in Data Validation

    Thanks Aladin
    When I go to name manager and edit, the “Refers to” Box is greyed out and will not alow me to enter anything, then if I cancel to get out it gives me an error “The Name you entered is not Valid”
    I changed the name to “Custlist” as you suggested, the “refer to” box is still greyed out and if I cancel to get out I get the same name is not valid error.
    Very Strange.
    I have two named tables on one sheet, would that cause this problem?
    praemonitus, praemunitus

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Using a Named table in Data Validation

    Quote Originally Posted by Bagsy View Post
    Thanks Aladin
    When I go to name manager and edit, the “Refers to” Box is greyed out and will not alow me to enter anything, then if I cancel to get out it gives me an error “The Name you entered is not Valid”
    I changed the name to “Custlist” as you suggested, the “refer to” box is still greyed out and if I cancel to get out I get the same name is not valid error.
    Very Strange.
    I have two named tables on one sheet, would that cause this problem?
    Try the suggestion in a clean workbook in order to verify that Excel itself is not the problem.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using a Named table in Data Validation

    Thanks Aladin
    I think the problem may have been because I created a named table rather than range, what I have tried is as follows.
    1 Created a named range AY3 – AY9
    I then converted it to a named table (insert table and then named it)
    I then tried my data validation and entered =Customer_List into the source box and it worked perfectly.
    Thanks very much for your time and effort
    Gary
    praemonitus, praemunitus

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,117
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Using a Named table in Data Validation

    Gary,

    You want the the customer list to be dynamic, right? If so, try the suggestion I made. By the way, while dynamic a Table does not work as list source in data validation.
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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