Dynamic validatation based on previous validation list

[bujaman]

OK, here is what I am trying to accomplish: I have one cell (M12) that uses a data validation list that is tied to all the names of the worksheets in the workbook. I want the user to be able to select the worksheet they want to use in the first validation list, then have a second validation list update with data from the selected sheet. I have the following code, but something is not right.

Sub ValidateOptions()
Dim sheetname As String
Dim last

sheetname = Range("M12").Text
last = Range("X1").End(xlDown).Offset(0, 0).Row

    Range("M13").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='sheetname'!$X$1:$X$" & last
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

I get a 1004 application defined or object defined error on this line:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='sheetname'!$X$1:$X$" & last

I think it is in my Formula1:= section, something not right with how I am calling 'sheetname' into the code.

Any help is greatly appreciated! Thanks!

 

[Fowmy]

Put this line in this way:

Formula1:="=" & sheetname & "!$X$1:$X$" & last

 

[bujaman]

Thanks for the reply. That works, kind of. What I am looking for is a validation box that updates dynamically when another validation box is changed. I have uploaded a sample of what I am trying to accomplish here:
http://www.bujaman.com/wp-content/uploads/2010/08/Validation-Sample.xls
Again, any help is awesome!

 

[mikerickson]

If your first list (for cell M12) has a source like Sheet1, Sheet2, Sheet3

You could use =INDIRECT($M$12&"!$X$1"):INDEX(INDIRECT($M$12&"!$X:$X"),MATCH("zzz",INDIRECT(A1&"!$X:$X"),1)
as the source for the second validation list.

If your second source has numbers instead of text, use 99E+99 in place of "zzz"

 

[bujaman]

Thanks for the quick reply! My source for the first list is the name of the sheets in the workbook, which are not as simple as Sheet1, Sheet2, but are something like this: Traditions 1" and 2⅜" Woods, DayDream ¾ Sngl& ⅜ Dbl SlideVue, etc. So a little more complex. Not sure if that makes a difference. Also, I know my way around excel pretty well, but I know nothing about the INDIRECT, INDEX, and MATCH functions. Can you explain them a little so I can understand what they are doing? Thanks again, I really appreciate it.

 

[mikerickson]

INDIRECT("Sheet1A1") returns the cell Sheet1!$A$1
INDEX returns a subrange of the first argument INDEX(Sheet1!B:B,3,1) returns Sheet1!B3
MATCH matches a value against an array of values MATCH("zzz", Sheet1!C:C) returns the last text entry in Sheet1!C:C.

More info can be found in HELP.

 

[bujaman]

Thanks! That is very helpful!