Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: drop down lists

  1. #1
    Guest

    Default

    I want to create several drop down lists
    some that rely on the selection from another
    drop down list. To simplify please follow:

    Upon selecting a choice from a list in cell #A1, I would like another list to choose from in a different cell, say A2. ie if I choose another selection from the first list in A1 mentioned, a different list appears in the second cell A2. Is this possible? How? Help!


    SECOND QUESTION

    I want to select a choice from a drop down list, and have that choice multiplied by the value in a different cell, but put the calculated answer in the same cell as the drop down list Is this possible ? HELP!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-14 14:46, Anonymous wrote:
    I want to create several drop down lists
    some that rely on the selection from another
    drop down list. To simplify please follow:

    Upon selecting a choice from a list in cell #A1, I would like another list to choose from in a different cell, say A2. ie if I choose another selection from the first list in A1 mentioned, a different list appears in the second cell A2. Is this possible? How? Help!


    SECOND QUESTION

    I want to select a choice from a drop down list, and have that choice multiplied by the value in a different cell, but put the calculated answer in the same cell as the drop down list Is this possible ? HELP!
    Concerning the first question:

    The list in A1 should consists of items/names each of which must refer lists of different items.

    If that is the case, you can use the List option in A2 with

    =INDIRECT(A1)

    as source.

    Regarding the second question:

    I'd use different cell where you multiply the selection from a data validation cell with a target cell (also in order to safegurad the integrity of data validation you apply).

  3. #3
    Guest

    Default

    On 2002-03-14 14:58, Aladin Akyurek wrote:
    On 2002-03-14 14:46, Anonymous wrote:
    I want to create several drop down lists
    some that rely on the selection from another
    drop down list. To simplify please follow:

    Upon selecting a choice from a list in cell #A1, I would like another list to choose from in a different cell, say A2. ie if I choose another selection from the first list in A1 mentioned, a different list appears in the second cell A2. Is this possible? How? Help!


    SECOND QUESTION

    I want to select a choice from a drop down list, and have that choice multiplied by the value in a different cell, but put the calculated answer in the same cell as the drop down list Is this possible ? HELP!
    Concerning the first question:

    The list in A1 should consists of items/names each of which must refer lists of different items.

    If that is the case, you can use the List option in A2 with

    =INDIRECT(A1)

    as source.

    Regarding the second question:

    I'd use different cell where you multiply the selection from a data validation cell with a target cell (also in order to safegurad the integrity of data validation you apply).
    thanks. Q. In regards to a list that refers to another list. How do I do this?

    #2 You suggest to use different cell. I have gone this way, but is there a way to put the answer in the same cell?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-14 15:08, Anonymous wrote:
    On 2002-03-14 14:58, Aladin Akyurek wrote:
    On 2002-03-14 14:46, Anonymous wrote:
    I want to create several drop down lists
    some that rely on the selection from another
    drop down list. To simplify please follow:

    Upon selecting a choice from a list in cell #A1, I would like another list to choose from in a different cell, say A2. ie if I choose another selection from the first list in A1 mentioned, a different list appears in the second cell A2. Is this possible? How? Help!


    SECOND QUESTION

    I want to select a choice from a drop down list, and have that choice multiplied by the value in a different cell, but put the calculated answer in the same cell as the drop down list Is this possible ? HELP!
    Concerning the first question:

    The list in A1 should consists of items/names each of which must refer lists of different items.

    If that is the case, you can use the List option in A2 with

    =INDIRECT(A1)

    as source.

    Regarding the second question:

    I'd use different cell where you multiply the selection from a data validation cell with a target cell (also in order to safegurad the integrity of data validation you apply).
    thanks. Q. In regards to a list that refers to another list. How do I do this?

    #2 You suggest to use different cell. I have gone this way, but is there a way to put the answer in the same cell?
    #1 Make a 1-column list, say, consisting of e.g.,

    List1
    List2
    List3

    Select the range where you put this list name it MainList via the Name Box on the Formula Bar.

    Make a 1-column list, say, consisting of

    a
    b
    c
    d

    Select this range and name it List1. Do the same for List2 and List3.

    The point is that MainList consists of names that refer to definite ranges which contain items for selection.

    In A1 you select 'List' for Allow in the data validation window and enter as source:

    =MainList

    In A2, you select again 'List' for Allow in the data validation window and enter as source:

    =INDIRECT(A1)

    #2 I guess it can be done with VBA (others might want to help you on this), but I still think it should not be done the way you want in view of integrity of data validation. I'd be confused when I enter or select say in D1 10 and it suddenly becomes 70 -- another reason for not to.

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    The first code is manual the second is automatic.

    Sub BuildList()
    'By Joe Was
    myState = "=" & Worksheets("Sheet1").Range("B1").Value
    With Worksheets("Sheet1").Range("D1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:= _
    xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = False
    .ShowError = False
    End With
    End Sub



    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("B1")) Is Nothing Then
    myState = "=" & Worksheets("Sheet1").Range("B1").Value
    With Worksheets("Sheet1").Range("D1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:= _
    xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = False
    .ShowError = False
    End With
    Else
    myState = "=" & Worksheets("Sheet1").Range("B1").Value
    With Worksheets("Sheet1").Range("D1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:= _
    xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = False
    .ShowError = False
    End With
    End If
    End Sub

    To use the code!
    Select an item in the dropdown in B1 (Like the: "State")
    H column contains the list names for the lists in columns I, J & K, H would be the State list range name for the City list in column I, J & K or more column?
    The list selected in B1 will set the dropdown values of D1 (Like the "Cities" in your selected State")

    So you have one dropdown in B1 and the other in D1. When you select from the dropdown in B1 your selection loads the range name for the list used for the dropdown in D1. My test is B1 is three States and D1 is the Cities in that selected State. Hope this helps? JSW

    P.S. The Private Sub automatically checks for a change in B1,s value and runs the macro to update D1.


  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Friend,
    I have one file uploaded at

    http://www.pexcel.com/download.htm

    here you download file

    changingcombo.zip

    You can study the code.

    i hope this was what exactly you wanted.

    i had made this combobox for a friend and has uploaded as example file.
    nisht
    http://www.pexcel.com

Some videos you may like

User Tag List

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
  •