Data validation list conotrols other DV list

[staticbob]

Hi,

I need a data validation list to populate another list.

I have this example that works on the data at the top left. Cell G12 is a data validation list that looks at row 1. Using the code below, the 2nd validation list (I2) then only shows the users at that site.

This works well. My problem is that I need this to work on the data format shown in A15:A25, not on the table format.

Any ideas ?

Thanks in advance
Bob

Dynamic data Val.xls
	A	B	C	D	E	F	G	H	I
1	SiteA	SiteB	SiteC
2	Jim	Kid	Mike
3	John	Kurt	Mark
4	Joe	Kim
5	Jerry	Kelly
6		Kristen
7
8
9
10
11							Site		Name
12							SiteA		Joe
13
14
15	SiteA	Jim
16	SiteA	John
17	SiteA	Joe
18	SiteA	Jerry
19	SiteB	Kid
20	SiteB	Kurt
21	SiteB	Kim
22	SiteB	Kelly
23	SiteB	Kristen
24	SiteC	Mike
25	SiteC	Mark
Blad1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DataCol As String, DataR As String

If Target.Address = "$G$12" Then
DataCol = Cells(2, WorksheetFunction.Match(Range("G12").Value, Rows(1), 0)).Address
DataR = Range(DataCol, Range(DataCol).End(xlDown)).Address

Range("I12").Value = ""

With Range("I12").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=" & DataR
.IgnoreBlank = True
.InCellDropdown = True
.ErrorTitle = " Unacceptable input."
.ErrorMessage = "Pick an item out of the list."
.ShowError = True
End With
End If

End Sub

 

[Juan Pablo González]

No need for code:

http://www.mrexcel.com/board2/viewtopic.php?t=40

 

[staticbob]

Thanks JPG,

Thats works great, but . . . . the named ranges are dynamic and change daily. I may have upto 250 different sites each day, each with upto 50 users. I would need to redefine 300 named ranges daily.

Could this still be done without code ?

Thanks in advance
Bob

 

[Juan Pablo González]

Ok, this is a more dynamic way, without code.

Assuming sheets like this:

Book1
	A	B	C	D
1	SiteA	SiteB	SiteC
2	Jim	Kid	Mike
3	John	Kurt	Mark
4	Joe	Kim
5	Jerry	Kelly
6		Kristen
7
8
Lists

and

Book1
	A	B	C	D
1	Site	Name
2	SiteA
3	SiteA
4	SiteA
5	SiteA
6	SiteB
7	SiteC
8	SiteB
9	SiteB
10	SiteB
11	SiteC
12	SiteC
Sheet2

where you define the appropiate sites with their names in the sheet 'Lists'.

Now, in Sheet 2, select cell B2, and define the following names (Insert | Name, Define...)

BigStr	=REPT("z",255)
Col	=MATCH(Sheet2!$A2,Lists!$1:$1,0)
List	=INDEX(Rng,2):INDEX(Rng,MATCH(BigStr,Rng))
Rng	=INDEX(Lists!$1:$65536,0,Col)
Sites	=Lists!$A$1:INDEX(Lists!$1:$1,MATCH(BigStr,Lists!$1:$1))

Now, select column A (still in Sheet 2), and go to Data | Validation. Select "List" and in the source box put

=Sites

Now, select column B, go to Data | Validation again, select "List" and in the source put

=List

and you should be set.

 

[Aladin Akyurek]

I didn't study yet Juan's proposal. An advantage of Juan's proposal is that it does not contain volatile functions compared to what follows...

DynamicSublists.xls
	A	B	C	D
1		$B$3:$B$6	$C$3:$C$7	$D$3:$D$4
2	Main	SiteA	SiteB	SiteC
3	SiteA	Jim	Kid	Mike
4	SiteB	John	Kurt	Mark
5	SiteC	Joe	Kim
6		Jerry	Kelly
7			Kristen
8
AdminLists

We need two definitions which must be done using Insert|Name|Define.

Activate Insert|Name|Define.
Enter BigStr as name in the box for Names in Workbook.
Enter the following in the Refers to Box:

=REPT("z",255)

Click Add.

Enter MainList as name in the box for Names in Workbook.
Enter the following in the Refers to Box:

=AdminLists!$A$3:INDEX(AdminLists!$A:$A,MATCH(BigStr,AdminLists!$A:$A))

Click OK.

The following steps define the dependent sublists using the Name Box on the Formula Bar. Since your site names can contain spaces, we must take that into account.

In B1 enter & copy across:

=ADDRESS(ROW(B3),COLUMN(B3))&":"&ADDRESS(MATCH(BigStr,B:B),COLUMN(B3))

Activate B1, go to the Name Box on the Formula Bar, type SiteA (without a space between "Site" and "A"), and hit enter. Repeat this procedure for Site B and Site C using C1 and D1.

Now you can set up on some sheet, say, Sheet2, data validation using the foregoing definitions...

Example: Let A2 and B2 on Sheet2 be the cells where we want to data validate...

Activate A2.
Activate Data|Validation.
Choose List for Allow.
Enter the following in the Source box:

=MainList

Click OK.

This gives us the main list from which to choose a site.

Activate B2.
Activate Data|Validation.
Choose List for Allow.
Enter the following in the Source box:

=INDIRECT(INDIRECT(SUBSTITUTE(A2," ","")))

Note the second round of evaluation with INDIRECT.

Click OK.

In B2 we get a sublist to choose from, which is dependent on the choice made in A2.

 

[staticbob]

Sorry Gents,

I have confused things with my original exhibit. My data actually appears in this format . . .

Book1
	A	B	C	D
1	SiteA	John
2	SiteB	Geoff
3	SiteC	Jayne
4	SiteB	Clare
5	SiteB	Steven
6	SiteA	Paul
7	SiteC	Humphrey
8	SiteA	Cilla
9	SiteB	Jack
10	SiteC	Dave
11	SiteB	Emma
12	SiteB	Max
13	SiteA	Steffan
14	SiteC	Sarah
15	SiteA	Sue
16	SiteB	Lee
17	SiteC	Pete
18	SiteB	Robin
19	SiteB	Gaylord
20	SiteA	Bill
Sheet1

I need one drop down list to select the Site, then another dropdown list to select from users on at that site.

The main problem is that this list is going to be dynamic, changing potentially each day. I could have 250 sites, each with 50 or so users.

Thanks again,
Bob

 

[staticbob]

Bump !

 

[bolo]

I have a method that uses another worksheet. Computationally it isn't the best but it does the job. Would you like me to send it to you? PM ur email if you do.

 

[Aladin Akyurek]

Sorry Gents,

I have confused things with my original exhibit. My data actually appears in this format . . .

For one thing, it requires a lot more processing...

I need one drop down list to select the Site, then another dropdown list to select from users on at that site.

The main problem is that this list is going to be dynamic, changing potentially each day. I could have 250 sites, each with 50 or so users...

Not that difficult to construct/adapt the system I already posted, but the sheer amount od sites would render ineeficient.

I were you, I'd study Bolo's offer. Another possibility that comes to mind is macros that use Advanced Filter to construct the required lists, which can be combined with the system I already proposed.

 

[angellim]

Hi all,
I used Juan's method to do the dependent list. It works.

But when I tried using it cross workbook, it cannot work.

Could anyone advise me how to use the dynamic range across the workbooks?

Thanks!