+ Reply to Thread
Results 1 to 19 of 19

Can't use Numbers in named range list for Dependent data validation lists

  1. #1
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Can't use Numbers in named range list for Dependent data validation lists

    Hey all,

    Have tried to google and seach with out any succes.

    My question is as follows:

    I have created a dropdown list with data validation. The list has products on it shuch as: "Solar Freedom S10". The dropdown list is in sheet 1 and the data to validate in another sheet(4).
    Now i also created a dependent data validation for a second list so users can only choose one option (a price) after choosing a option in list number one(which is a product).

    I followed the tutorial on: http://www.contextures.com/xlDataVal02.html#TwoWord

    To be able to create the data validation dropdown list, the name from my range(one cell i my case) should be exactly the same as the matching entry. This i can't because in my case these are numbers,... € 4,112.23 te be exact...

    The tutorial says:
    "2. Create the supporting Named Lists

    Type the entries you want to see in the Excel data validation drop-down list for one of the Product categories.
    Select the cells in the list.
    Click in the Name box, to the left of the formula bar
    Type a one-word name for the list, e.g. Fruit. This name must be exactly the same as the matching entry in the Produce list.
    Press the Enter key.
    "

    You can see my excel file included.

    What i want to achive is whenever a user selects a option (product) in list 1: The variables of the following lists are depenent on it. It would even be better that if the product is selected the following cells are filled with some information which is on a second tab(the all are prices).

    Any suggestions?
    Attached Files Attached Files
    Last edited by Sam Kuiper; 11-11-2012 at 07:30 PM. Reason: [SOLVED]

  2. #2
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    SOLVED: Can't use Numbers in named range list for Dependent data validation lists

    Solved

    What i needed was a Vlookup formula =]
    =INDIRECT(VLOOKUP(A2,NameLookup,2,0))

    Using Items with Illegal Characters

    You may need items in the first Excel data validation drop-down list that contain characters not allowed in range names, such as the ampersand (&).
    From: http://www.contextures.com/xlDataVal02.html#TwoWord

    Now i still have one question, i would like an autofill option for when a user chooses a Product from the first drop down list with data validation. I would like the rest of the lists which are dependent on each other to auto fill..

    So for instance:

    Select in cell B6:
    Solar Freedam 6Sx

    CellB7: € 3,397.14
    CellB9: € 2,897.14

    (these prices should be Validated aswell and Dependent!)

    Any suggestions?

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Your sample sheet indicates a drop down to select the Solar unit, and then there is only ONE possible price for each selection. This means you do not need, nor should you use, a drop down validation for the price. Use a simple VLOOKUP formula in the cell to cause the price to appear on its own.

    B7: =IF(B6="", "", VLOOKUP(B6, 'Totaal prijs'!A:C, 3, 0))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't use Numbers in named range list for Dependent data validation lists

    1st, you dont need DV or a dropdown for this, you can use a simple vlookup()

    2nd, i have no idea where you arrive at a value of 3397.14 for B7, that is not in any of the cells in you'r workbook.

    for B9 use =VLOOKUP(B6,'Totaal prijs'!A5:C10,3,FALSE)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Quote Originally Posted by FDibbins View Post
    1st, you dont need DV or a dropdown for this, you can use a simple vlookup()

    2nd, i have no idea where you arrive at a value of 3397.14 for B7, that is not in any of the cells in you'r workbook.

    for B9 use =VLOOKUP(B6,'Totaal prijs'!A5:C10,3,FALSE)
    Great thanks! This is even better than what i asked for

    What if i want to do the same with an other price to auto fill in for instance B9. The prices are on Sheet: Totaal prijs
    B13:B18? (second attachment)

    Thanks
    Attached Files Attached Files
    Last edited by Sam Kuiper; 10-28-2012 at 08:22 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't use Numbers in named range list for Dependent data validation lists

    the vlookup will change every time you change the value sheet1 B6 dropdown.

  7. #7
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Quote Originally Posted by FDibbins View Post
    the vlookup will change every time you change the value sheet1 B6 dropdown.
    Oke thanks, ment that what if I want in B10 a other prize to be autofilled when ever the drop down is selected. Besides the prize in B9.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't use Numbers in named range list for Dependent data validation lists

    in the sample you provided, that was the only range of prices you gave. however, if you have other proces/data you want to find, vlookup will be able to do that for you. the syntax for using vlookup() is as follows...

    =VLOOKUP(what-you-want-to-find, range-to-search-in-with-match-in-first-column, column-that-contains-the-data-wanted, FALSE)
    FALSE is for finding an exact match

    if you still have a problem, lety me know
    Last edited by JBeaucaire; 10-28-2012 at 09:38 PM. Reason: Corrected parameter definitions

  9. #9
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Can't use Numbers in named range list for Dependent data validation lists

    It works like a charm! Thank you guys!
    There is still one thing i do not grasp. The formula works if i put the name of the first data say: Solar Freedom S6. In A5 and the price in C5 but it does not work when i put the price in B5. (and yes i changed the formula to:
    Please Login or Register  to view this content.
    What causes this?

    Other small question: I have two random words one in B6(Solar Freedom 6S) and in E6( Incl. Installatie en BTW) than i have in cell F6 the following formula:
    Please Login or Register  to view this content.
    . Now the copy works only the Italic fond is gone... :'( How to overcome this?

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Sam, you need to take a few moments in Excel, press F1, and read very adequate builtin tutorial on VLOOKUP, it explains the issue quite efficiently and briefly.

    Answer = your 3rd parameter must be changed, too. See post #8.

  11. #11
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Question =INDIRECT for a VLOOKUP Formula? need different prizes with a different dropdown option.

    Jbeaucaire,

    Thanks your right. Its quite logical, have it figured out now.

    On to the next stumble block:
    You can see the excel attached. I have a great Validated dropdown list with: Plat dak and Schuin Dak in B5.(validation from other tab) Than with a
    Please Login or Register  to view this content.
    code for a product article B7. From there on the rest is done with VLOOKUP which fills in several prices.

    This is great and thank you guys for helping me out so far!

    Now i would like to have the VLOOKUP go for a different sheet after PLat Dak is chosen instead of Schuin Dak. The range could still be the same and also the parameter. So is there any way to use a indirect methode for the sheet to which the Vlook up is heading?
    For example: B8 has this formula:
    Please Login or Register  to view this content.
    when i choose Schuin Dak - Solar Freedom 8S - I'll get a good price in this cell =]
    When i go for Plat Dak - Solar Freedom 8P It will give the price of Schuin Dak. And i want to to look in the sheet Prijs Plat console; As follows:
    Please Login or Register  to view this content.
    Invul lijst.xlsm
    Last edited by Sam Kuiper; 11-09-2012 at 05:01 PM.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't use Numbers in named range list for Dependent data validation lists

    i have a few questions for you...
    both of you're ranges Schuin dak and Plat dak have exactly the same data (they even reference the same cell in another workbook on you're C drive), so why have 2 different ranges? In fact, you reference the same into in 13 different columns, seems a bit redundant to me?

    you have multiple entries that are the same in each column - "design volt" if the data is the same from row 20 onwards, why have the repetition?

    to answer you're question, you could make up range names for the other ranges/sheets, and then, within the vlookup() use indirect() to refence them, something like...
    =VLOOKUP(B7;indirect(Totaal_prijs);2;False)

  13. #13
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Quote Originally Posted by FDibbins View Post
    both of you're ranges Schuin dak and Plat dak have exactly the same data (they even reference the same cell in another workbook on you're C drive), so why have 2 different ranges? In fact, you reference the same into in 13 different columns, seems a bit redundant to me?
    Have not finished the workbook yet. What about the same data thats not correct as you can see:
    Please Login or Register  to view this content.
    for Schuin Dak and:
    Please Login or Register  to view this content.
    Its the same Workbook only it refers to another sheet. One sheet has the prices of Schuindak and the other holds those for Plat dak.

    Quote Originally Posted by FDibbins View Post
    In fact, you reference the same into in 13 different columns, seems a bit redundant to me?
    This is indeed redundant. I did it beceause, i couldn't figure out the Vlookup (bit hasty work now i have read a bit about it and made it work.) See the new workbook i attached.

    Quote Originally Posted by FDibbins View Post
    you have multiple entries that are the same in each column - "design volt" if the data is the same from row 20 onwards, why have the repetition?
    Again the same awnser, the workbook is not yet finished. Those names will change to Solar Freedom XS (with x the product number).

    Quote Originally Posted by FDibbins View Post
    to answer you're question, you could make up range names for the other ranges/sheets, and then, within the vlookup() use indirect() to refence them, something like...
    =VLOOKUP(B7;indirect(Totaal_prijs);2;False)
    Okay, many thanks for the time so far! Really apreciate it and i'm growing in my excel skills alot due to the help all of you have given me sofar. I tried to implement your advice, you can see it in the attached workbook. I tried it in Sheet 1 cell B8. It gives a REF sign. I have cleaned up the workbook a bit as you can see.

    Edit Invul lijst.xlsm

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't use Numbers in named range list for Dependent data validation lists

    no, you need to create a new named range for this. to keep it simple, give it a name that you will be using from the drop-down. then you can use it inthe indirect() function as the lookup range for the vlookup()

  15. #15
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Quote Originally Posted by FDibbins View Post
    no, you need to create a new named range for this. to keep it simple, give it a name that you will be using from the drop-down. then you can use it inthe indirect() function as the lookup range for the vlookup()
    Okay, as i interpated this is as follows: I created a namedrange from: A5 to AA10. The namedragne i called: Totaal
    Than i put the code in as you gave me:
    Please Login or Register  to view this content.
    Although i had to replace the ; for ,
    Now it still gives a Ref! sign. (if i delete the named range it gives a NAME sign. )

    The Product which i choose in B7 is either in A5 to A10 or in the case of Plat dak in: P5 to P10. So i used the index comn 3, i expect to calucate from the column in where it finds a match within the named range...

    So where do i still miss out?
    Edit Invul lijst.xlsm

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't use Numbers in named range list for Dependent data validation lists

    ok sorry, I thought you were trying to reference Totaal from another cell. try this instead, without the indirect()

    =VLOOKUP(B7,Totaal,3,FALSE)

  17. #17
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Quote Originally Posted by FDibbins View Post
    ok sorry, I thought you were trying to reference Totaal from another cell. try this instead, without the indirect()

    =VLOOKUP(B7,Totaal,3,FALSE)
    Hmm this sets it back to normal. It makes the cell respond fine to all options from the dropdown which are generated when i choose Schuin Dak. These opions are on sheet Totaal prijs - A5 to A10.

    While when i use those which are generated when i choose Plat dak it doesn't (propably because these are in P5 to P10 on the sheet Totaal prijs. And not in the first column of the range...).

    To illustrate:

    Dropdown one:
    Plat dak
    Dropdown 2:
    Sun EV 6 - on Totaal Prijs P5 (included in the named range Totaal)
    Cell in which contains =VLOOKUP(B7,Totaal,3,FALSE):
    #N/A

  18. #18
    Registered User
    Join Date
    10-27-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Can't use Numbers in named range list for Dependent data validation lists

    Quote Originally Posted by FDibbins View Post
    you could make up range names for the other ranges/sheets, and then, within the vlookup() use indirect() to refence them, something like...
    =VLOOKUP(B7;indirect(Totaal_prijs);2;False)
    I finaly got it i invoked the following into the cell wich needed the vlookup(B8):
    Please Login or Register  to view this content.
    Than wha i did as you suggested, was to give the product a namerange. Both Schuin dak and Plat dak. Now it works =D Many many thanks! Also for making me clean my workbook x'D looks alot better now.
    Here is the result: http://www.4shared.com/file/ghWDaWVv/Invul_lijst_2.html

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't use Numbers in named range list for Dependent data validation lists

    glad i was able to help, and thanks for the rep

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1