drop down lists

G

Guest

Guest
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!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top