Pull down cells?

[gacollier]

This is likely an easy deal, so bare with me please...
Here's my dilema, I have a worksheet with hundreds of pipe fitting data. Dimensions, descrioptions, and prices. I would like to be able to go to a blank worksheet and by using some sort of pull down, select one of the fittings from my list and bring in only that fitting data. The step down to the next line and do the same, making a much shorter list, but using the data from the base list.

If you have an example of this you're already using I would appreciate a copy. E-mail is: greg@dsiprocess.com

Thanks!

 

[Joe Was]

You may want your mouse to do the typing for you?
If you have the same data, over and over again you may want to use dropdown list, to load the cell or cells for you?

Below are two types: a list of items that pulldown from within the cell that gets the data and be able to add your own item, that is not on the pulldown-dropdown list, then:

To the right of your sheet build a list one item per row in one column. Click the column ID to highlight the column, then use:

Insert-Name-Define then name your list.

Click the cell you want the list in, then:

Data-Validation-Settings (select "List" from Allow) in "Source" add =your list name, Like: =myList

Then copy the cell you just put the dropdown list in and highlight the other cells you want the list in and hit special paste-validation.

Now when a cell with your dropdown is selected, it grows a dropdown arrow, which when clicked returns your selection list.

To limit the selection to only the values in your list:

Data-validation-Settings
Allow: List
Check: Ignore blank
Check: In-Cell dropdown
Source: =$AA:$AA (this is the column "myList" is in.

Then:

Tab to Error Alert
Check: Show
Style: Stop
Title: Error!
Error Message: Only select from the dropdown list!

Now the user can put a wrong entery in the cell but they cannot move from the cell without an Error Box Message and the options to fix or erase their entery or start over.

 

[Joe Was]

You can also do this with a drop-down list, if your values are hard coded in a lookup list. The code below will use one list to lookup values in another list.

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.