drop down lists

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!

[Aladin Akyurek]

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).

On 2002-03-14 14:58, Aladin Akyurek wrote:

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?

[Aladin Akyurek]

On 2002-03-14 15:08, Anonymous wrote:

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.

[Joe Was]

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.

[nisht]

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