Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am new here and this is my first post. I am an excel power user with some knowledge of VBA.

    I have a worksheet that has 2 drop down list cells. Depending on what is selected in the first drop down, will determine which list is displayed in the 2nd drop down list.

    I have my lists on a separate sheet within the same workbook. The list is named and ready to use.\

    Hope someone can get me started.

    Thanks,
    Cindy

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='Honeyrun' post='769066' date='03-Apr-2009 16:09']Hope someone can get me started.[/quote]
    Welcome to the lounge,

    This spreadsheet uses Data Validation to populate two listboxes

    [attachment=83203:Listboxes.xls]
    • The first listbox is in A5, and its validation list is set to a range called List1, which is at C4:C6 on the sheet called lists.
    • The second listbox is in B5, and its validation list is set to a range called List2, which is at D10:H10 on the sheet called lists.
    • The cells at D10 to H10 on the lists sheet are populated by a Vlookup based on what is currently selected in A5
    Is this what you were looking for?
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Stuart,

    That is partially what I am looking for.

    How do I create the VLookup for the cell that is populated depending on what is in the first cell?

    Thanks,
    Cindy

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='Honeyrun' post='769541' date='07-Apr-2009 14:43']Stuart,

    That is partially what I am looking for.

    How do I create the VLookup for the cell that is populated depending on what is in the first cell?

    Thanks,
    Cindy[/quote]
    Did you try changing the contents of cell A5 and then looking at the dropdown list for B5?
    I think this is what you are asking for.
    The validation list for B5 points to a range that is populated by a VLookup
    The VLookup uses the contents of cell A5 to populate the range that B5 uses for its validation list.

  5. #5
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes,

    But what cell do I put the Vlookup in? I tried using the wizard, but it wanted mulitple columns and I only want one column. It appeared to look for a "junction" result and not a random result.

    Here is the list.

    If Sheet2.E2 (This list is on Sheet1)= Capital Blue Cross, then the Pick List in E3 should come from Sheet1.C2:C35

    I will have many other lists that will be used as column C is being used. With each "Carrier" there will be a separate picklist to choose "programs".

    See uploaded file.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    There are a couple of problems with your Lookup_Test.xls that make it very hard to fix this for you.
    • The dropdown lists are in Merged cells, this creates problems for all sorts of formulae, it would be much better if you moved these to cells that are not merged
    • The validation ranges for your dropdown lists are all pointing to named ranges that are on a worksheet called Picklist in a workbook called Master.XLS but you have not provided this workbook
    • Your description talked about two dropdowns but the spreadsheet has three, what relationship should there be between these?
    • If the Carrier Names on Sheet1!B2:B17 are supposed to be in the first dropdown then you need to provide a column with values for each name in the list at Sheet1!B2:B17. You have provided values in column C for a carrier called Capital Blue Cross, but column D has data for a carrier called Capital Blue Cross RX (which doesn't exist in the list in column and there are no corresponding columns for all the other carriers in column B.
    If you can provide a spreadsheet that has the data you want to use then I can try to show you how to do this with your data.

  7. #7
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Stuart,

    I can email you the original file, but can not post it here.

    The first Drop Down list is for information only and doesn't need to point to any other cell.
    If you see the original you will see the connections.

    Please send my your email to cstahle@glatfelters.com and I will send the original .xls

    Thanks,
    Cindy

  8. #8
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I have made a few changes to your sheet, including redefining the names Carrier and CBC to point to ranges on Sheet1. Does this do what you are looking for?

    You will need to add more columns to Sheet1 for the various options.

    [attachment=83237:Lookup_Test.xls]
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Stuart,

    Yes, that is it. But I don't see where you made the changes to the cells to do what you wanted them to do. Sorry I am such a dunce at this it has been many years since I have had to work in excel to this level and MS has made many changes.

    Here is the original .xls

    As you can see the Single sheet is the one I am trying to get working and the Picklist is where the cell drop list information resides.

    Once I understand what you are doing, I will be able to do it to the rest of the columns.

    Thanks for you help,
    Cindy
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Cindy,

    This is rather different to the example as you now have multiple pairs of check boxes, and you need to create a separate validation range for each of these.

    Here is how you can modify your master.xls to do what you want.
    • Leave the range called Carrier alone and use this as the validation list for Single!E5:N5 and for Single!E43:N43
    • Modify the cells in Picklist!C1:Q1 so that they are identical to the values in B2:B17, for example by changing "Capital Blue Cross (CBC)" to "Capital Blue Cross" and changing "Aetan" to "Aetna"
    • Delete the named range called CBC as we are going to create a separate validation range for each place this one was previously used.
    • Define a new named range, called CBC that includes Picklist!B1:Q35
    • We are going to use Sheet1!E82:E115 as the validation list for Sheet1!E5
      • In Sheet1!E82 put the formula =HLOOKUP(E$4,CBC,ROW(E82)-80,FALSE)
      • drag this formula down to fill all the way to E115
      • Set the validation list for E5 to be E82:E115
    • We now have to copy this validation list and paste it for other locations
      • Select cells E82:E115
      • Copy
      • Move cursor to G82
      • Paste
      • Copy Cell E5 and paste special > Validation to copy the validation to G5
      • Similarly create validation lists for I5, K5 and M5
      • Now copy the whole range E82:M115
      • Paste these cells to E121, these are going to be the validation ranges for the dropdowns in row 44
      • With the cells that you pasted still selected type Control-H (or Edit Replace)
        • replace $4 with $43 in all the newly pasted cells
        • replace 80 with 119 in all the newly pasted cells
      • Select cells E4:N5, copy them and paste special > Validation to cells E43:N44
    You have now set up all the validation ranges you need on the sheet called Single.

    You still need to populate all the data on the Picklist sheet, and create similar validation ranges on any other sheets.
    I have attached your Master.XLS with these changes applied.

    There is probably a far easier way to do this, so do keep an eye on this thread in case one of our Excel gurus turns up and shows you a more elegant solution.
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    WOW, you are the MAN!

    Thanks so much,
    Cindy

  12. #12
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I'm going to move this thread from our VBA forum to the Excel forum, as this ended up not needing VBA.

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If I've understood correctly, you can do it all with 2 defined names as in the attached, but you'll need to move the data for the 'Capital Blue Cross RX (CBCrx)' plan.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    New Lounger
    Join Date
    Apr 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There will be a new set of selections for the "RX" plans.

    Also, There will be data to copy and paste depending on what is in the 3rd drop down cell. I work on that next after I get all the data for the initial ones.

    Cindy

  15. #15
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='rory' post='769688' date='08-Apr-2009 13:42']If I've understood correctly, you can do it all with 2 defined names as in the attached, but you'll need to move the data for the 'Capital Blue Cross RX (CBCrx)' plan.[/quote]
    Rory,

    Your very elegant solution includes the defined name Planlist, which equates to
    =OFFSET(Picklist!$B$2,0,MATCH(Single!E4,Carrier,0) ,COUNTA(INDEX(Picklist!$C:$Q,0,MATCH(Single!E4,Car rier,0)))-1,1)
    Could you please explain how this works, as a learning exercise for those of us who might learn from this.

Page 1 of 2 12 LastLast

Posting Permissions

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