Pull down cells?

gacollier

New Member
Joined
Sep 9, 2002
Messages
4
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,429
Messages
5,572,059
Members
412,437
Latest member
PietTheijssen
Top