[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

pick an item from a list so that it gives me a range somewhere else

hi Folks
Just wondering if it's possible to do the following..
User picks items from a list, and then the items that match that category appear on another worksheet. I've attached an example to illustrate. Thank you. I suspect it's something to do with the index function :-)
0
agwalsh
Asked:
agwalsh
  • 5
  • 5
1 Solution
 
Shanan212Commented:
Please post your sample worksheet
0
 
agwalshAuthor Commented:
Well doh...and I'm not even blonde....
producing-a-filtered-list-in-ano.xlsm
0
 
Shanan212Commented:
See attached. Since your input and lists were confusing, I created a worksheet to explain.

Click on the selection and result boxes and then click 'data validation' to see how it works.

The namelists have to be same as your selections
producing-a-filtered-list-in-ano.xlsm
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
agwalshAuthor Commented:
Cool...but just wondering would it be possible to produce a range from this answer. See attached file.
producing-a-filtered-list-in-ano.xlsm
0
 
Shanan212Commented:
See attached

So it monitors for change in cell E3 (you can change this) Then inserts the named list starting from row 3 (here its monitored by variable called 'C')

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, C As Long, sVal As String
    Application.EnableEvents = True
    
    If Target.Address = [E3].Address Then
        C = 3
        sVal = Range("e3").Value
        Range("F3:F15").ClearContents
        
        For Each rng In Range(sVal)
            If Len(rng) > 0 Then
                Range("F" & C) = rng
                C = C + 1
            End If
        Next rng
    End If
 
End Sub

Open in new window

producing-a-filtered-list-in-ano.xlsm
0
 
agwalshAuthor Commented:
@shanan212 - looking good (brilliant actually :-) ). I want to experiment with this a little so
Couple of other questions...
Can I create the named ranges as dynamic ranges (either using offset or some variation of the table facility) so that if the lists get longer, the dropdown adjusts automatically.
Where do I put the above code? This worksheet or the VBA window of one of the sheets?
thank you (as always...)
0
 
Shanan212Commented:
The code works with dynamic ranges. This line

For Each rng In Range(sVal)

looks for each cell in the range that belongs to the specific named range aka 'sVal'

To put the code, go to the worksheet where all this happens, then right click on the worksheet tab (where the worksheet name shows) and click 'view code'. In the window that opens, put the code.
0
 
agwalshAuthor Commented:
How would I amend the code so that I can specify the sheet I want the result to go on?
so what do I need to add to this code to specify a particular sheet (other than the one it's on)
  sVal = Range("e3").Value
        Range("F3:F15").ClearContents

thank you :-)
0
 
Shanan212Commented:
The whole sub is called a 'worksheet change' event. In this case, the event has to be on the sheet where the event happens.

if you want to show the results to show on different sheet, try this


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, C As Long, sVal As String
    Application.EnableEvents = True
   
    If Target.Address = [E3].Address Then
        C = 3
        sVal = activesheet.Range("e3").Value
        sheets("YOURSHEETNAME").Range("F3:F15").ClearContents
       
        For Each rng In Range(sVal)
            If Len(rng) > 0 Then
                sheets("YOURSHEETNAME").Range("F" & C) = rng
                C = C + 1
            End If
        Next rng
    End If
 
End Sub


-----------

On the above code, change these 3 lines

1. Change sheet name and range and monitor where the range starts

sheets("YOURSHEETNAME").Range("F3:F15").ClearContents

2. Change the 'F' in the below code to the range column

sheets("YOURSHEETNAME").Range("F" & C) = rng

3. Change the 3 to whereever the range  starts (eg: F3 in above case)

C = 3
0
 
agwalshAuthor Commented:
Thank you :-)
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now