Akash,
This is a little complex, but seemed to work for my test book. The generalidea was to use SQL to query the "Database" tab for the relevant information and populate the data validation lists accordingly. There are two bits of code here: one to create the list and the other to trigger a change to the lists.
First, add the macro called "ValidationList" to Module1 of your project. This code assumes that your Products are in column A of the "Database" sheet and that Vendors are in column B of the same. You will need to adjust the "SELECT CASE" section of the code if these assumptions are incorrect for your data.
Next, add the "Worksheet_SelectionChange" macro to the "Workings" sheet code module. This macro assumes that the Product list will be in cell B5 and the Vendor list in cell B6. Again, change these as appropriate.
If all goes according to plan, you can simply select the cell and it will automatically find the appropriate list for you.
Hope this helps,
Ben
'Place the code below in Module1
Sub ValidationList(sType As String, rCell As Range, Optional sProduct As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim vArray As Variant
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
Select Case sType
Case "Product"
strSQL = "SELECT DISTINCT * FROM [Database$A:A]"
Case "Vendor"
strSQL = "SELECT DISTINCT Vendor FROM [Database$A:B] WHERE Product = " & _
Chr(34) & sProduct & Chr(34)
Case "AllVendors"
strSQL = "SELECT DISTINCT * FROM [Database$B:B]"
Case Else
cn.Close
Exit Sub
End Select
rs.Open strSQL, cn
With rCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=rs.GetString(adClipString, , ",", ",")
End With
cn.Close
End Sub
'''''''''''
'Place the code below in the "Workings" Sheet's VBA module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rProduct As Range, rVendor As Range
Set rProduct = Range("B5")
Set rVendor = Range("B6")
If Target.Count > 1 Then Exit Sub 'Exit if multiple cells selected
'Populate product cell or vendor cell as applicable
If Not Intersect(rProduct, Target) Is Nothing Then
Application.EnableEvents = False
ValidationList "Product", rProduct
ElseIf Not Intersect(rVendor, Target) Is Nothing Then
Application.EnableEvents = False
If rProduct <> "" Then
ValidationList "Vendor", rVendor, rProduct.Value
Else
ValidationList "AllVendors", rVendor
End If
End If
Application.EnableEvents = True
End Sub
