Avatar of gilnari
gilnariFlag for United States of America asked on

Limit Data Validation list

I have two worksheets, Worksheet A and Worksheet B.

In Worsheet B, I have two columns.  In the first column I have list of records all unique in value.  In the second column thy are have  value that defines a type of record for the first column and contains values like  R, N, P, T.

In Worksheet A, I want to create a Data Validation list from the records in Worksheet B where the values listed in first Column are equal to the value R from the second columns.

Also using MS Excel 2003

something like
=WKSheetB!$L$10:$L$439 where WKSHeeTB!$M$10:$M$439=R

I know this is no way close to right but any help be greatly appciated.
Microsoft Excel

Avatar of undefined
Last Comment
gilnari

8/22/2022 - Mon
Patrick Matthews

gilnari,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick
ASKER
gilnari

I have attached a very very water down version of what I am trying to accomplish.

In the First worksheet Tet I have a list of Comnpanies and I want to choose from the drop down list in Column B only those items that have analysis of R from worksheet B.  This is very water downed as the second worksheet has thousand of records so setting up independt list to referent may not be an option.
Figure-Out-how-to-Filter-a-Named.xls
dlmille

I've created a solution, leveraging some techniques I talk about in a recent article, http:\A_6429.html.  I've culled the code back to the minimum needed to screen out all from the Analysis range, based on the Criteria "R" offset exactly 1 column to the right.  Should you wish to, I could add additional code to return a sorted list.  However, I've kept this solution as simple as possible.

Based on a worksheet change event, the application drops a populated combobox down on top of any validation list - in this case, the application first screens the data validation formula =Analysis (one column to the right) for the value "R", then adds the items to the combobox.  You can use the combobox just as you would use the DV list, but with the combobox, we have a bit more flexibility as we can load it based on these rules.

Here's the code that goes in the ThisWorkbook codepage (hit alt-F11 and paste this in ThisWorkbook):
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Uncomment the below code and comment out the code in Combo Box and DV Lists, if you only want this feature on the current sheet

    Call DataValidationManagement(Target)
    
End Sub

Open in new window


This routine calls a subroutine called DataValidationManagement() which does most of the work - this code is pasted in a public module:

 
Option Explicit
Sub DataValidationManagement(ByVal Target As Range)
'The below called from "change" events associated with Data Validation Lists
'Original Concept for Data Validation/ComboBox drop down from http://www.contextures.com/xldataval10.html

Dim str As String
Dim cboTemp As OLEObject
Dim vType As Variant
Dim chkDVList As Variant
Dim proceedSetup As Boolean
Dim myCell As Range

   
    On Error Resume Next 'connect to temporary ComboBox "TempCombo", testing along the way using Err.Number
    
    Set cboTemp = ActiveSheet.OLEObjects("TempCombo")
    If Err.Number <> 0 Then 'the ComboBox object must have been inadvertently deleted, so let's create it
        Set cboTemp = ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1")
        cboTemp.Name = "TempCombo"
    End If
    
    proceedSetup = False
    vType = Target.Validation.Type 'Empty if there is no validation type at Target address
    If Left(Target.Validation.Formula1, 1) = "=" Then 'check to see if there's a validation reference to a range address
        proceedSetup = True
    End If
    
    On Error GoTo 0
    
    On Error GoTo errHandler 'fall to bottom on any errors
      
    If Not IsEmpty(vType) And vType = 3 And proceedSetup Then 'Target.Validation.Type = 3 Then 'if the cell contains a data validation list
        Application.EnableEvents = False
                        
        'get the data validation formula
        str = Target.Validation.Formula1
        
        chkDVList = Evaluate(str)
        
        str = Right(str, Len(str) - 1)
        
        If Not IsError(chkDVList) Then 'must be a range reference in the Data Validation list; else just use regular data validation, then
            'load the DV List based on the "R" criteria, using cboTemp comboBox
            
            With cboTemp
                'show the combobox with the list
                .Visible = True
                .Left = Target.Left
                .Top = Target.Top
                .Width = Target.Width + 5
                .Height = Target.Height + 5
                .ListFillRange = ""
                .LinkedCell = Target.Address
            End With
        
            'traverse the data validation list, looking one column to the right for the ("R") to see what gets added
            For Each myCell In Range("'" & Evaluate(str).Parent.Name & "'!" & Evaluate(str).Address)
                If myCell.Offset(0, 1).Value = "R" Then
                    cboTemp.Object.AddItem myCell.Value
                End If
            Next myCell
        Else
            'do nothing
        End If
    Else
        With cboTemp
            '.Clear
            'hide the combobox, and get it out of the way from inadvertent deletion
            If .Visible = True Then
                .Visible = False
                .Left = Range("BB5000").Left
                .Top = Range("BB5000").Top
            End If
        End With
    End If
        
errHandler: 're-enable events
    Application.EnableEvents = True
    Exit Sub
End Sub

Open in new window


As you can see in this code, we drop a combobox ("TempCombo") on top of the data validation cell, then we test the data validation list (one column to the right) for the "R" key.  If found, we add it to the combobox.

See attached demo worksheet.  Just click in the data validation cells, and you'll see the drop-down now meets your criteria.  Note, this function will persist for ALL data validation lists in the workbook.  You can narrow this to a specific sheet by adding a line to the ThisWorkbook Code as follows:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'Uncomment the below code and comment out the code in Combo Box and DV Lists, if you only want this feature on the current sheet

    If Sh.Name = "SpecialValidation" Then 'just change "SpecialValidation" to the sheet name where you want this special function to exist
        Call DataValidationManagement(Target)
    End If
   
End Sub


Please advise if more assistance is required.

Enjoy!

Dave
Figure-Out-how-to-Filter-a-Named.xls
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
gilnari

Hi Dave,

Yup got it mostly to work and was able to expand on it.  However one minor issue the records to chose from the list repeant and each time you add a new record to to Test and choose a value from the list it seems to repeat iteslf.  Almost seems for each new record there needs to be a rebuild of the combobox.  Any ideas?
dlmille

Yes, the combobox instantiates itself and refreshes based on the data validation formula each time.  That's the design.  I could provide a more customized version, if theres a noticeable delay you're dealing with.

Dave
ASKER CERTIFIED SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
gilnari

Thank you very much, you have no idea how this saved me.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.