Generating Lists

[Randy Rush]

I have a list of contractors with names address etc., all
with a categories applied. What I want to do is in cell
B1 be able to pick the category "Flooring", then in C1
have the list of the Flooring contractors displayed for
the user to pick. Any help would be greatly appreciated.

Randy

[Gord Dibben]

Randy

Debra Dalgleish has some very good info and examples on how to set up in-cell
drop-downs dependent upon choices made in other drop-downs.

Once you pick your "Flooring" category, the other drop-down will supply the
"Flooring" contractors by name. To get addresses, phone numbers etc. you
might have to set up VLOOKUP formulas in adjacent cells.

HTH Gord Dibben Excel MVP - XL97 SR2

[Damon Ostrander]

Hi Randy,

Here's an example of how to do this. The following
worksheet Change event code will update the dropdown list
in C1 whenever a value is selected from the dropdown list
in B1. These dropdown lists are assumed to be cell
Validation lists, and for this example I assume the B1
dropdown (validation) list is defined in range Am:An
(where m and n are row numbers), and the dropdown lists
for C1 are defined in ranges Bm:Bn2, Cm:Cn3, Dm:Dn4, etc.,
where m is the same row that the B1 list starts in, but
n2, n3, n4 can be any row numbers greater than m. It
doesn't matter what row you start all the lists in because
the macro looks at the validation for cell B1 to find out
where it starts, and assumes all the other lists start on
the same row. Set up the Validation for C1 to use the
first list (Bm:Bn2) before installing the macro. Install
this macro in the worksheet's event code area by right-
clicking on the worksheet tab, select View Code, then
paste the code in the VBE code pane, and viola! The
validation list in C1 will now automatically change
whenever a selection is made in the B1 validation list.

Keep Excelling.

Damon

PS. If this is too confusing, feel free to ask for an
example by email at VBAe...@piadamon.com

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ListItem As Integer
If Target.Address = [b1].Address Then
With Range(Target.Validation.Formula1)
ListItem = .Find([b1].Value).Cells.Row - .Row + 1
[c1].Validation.Modify xlValidateList,
xlValidAlertStop, xlEqual, "=" & .Offset(0,
ListItem).Resize(.Offset(1000, ListItem).End(xlUp).Row -
.Row + 1).Address
End With
End If
End Sub

>.
>

[Dave Peterson]

and her site is:

http://www.contextures.com/xlDataVal01.html

--

Dave Peterson
ec3...@msn.com

[Gord Dibben]

Good pick up Dave. Senior moment over here, I 'spose.

Thanks, Gord