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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

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.
 

Forum statistics

Threads
1,147,512
Messages
5,741,594
Members
423,671
Latest member
ProSafe

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
Top