Solved

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

Posted on 2013-01-24
10
234 Views
Last Modified: 2013-01-31
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
Comment
Question by:agwalsh
  • 5
  • 5
10 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38815339
Please post your sample worksheet
0
 

Author Comment

by:agwalsh
ID: 38815393
Well doh...and I'm not even blonde....
producing-a-filtered-list-in-ano.xlsm
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38815556
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
 

Author Comment

by:agwalsh
ID: 38815884
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
 
LVL 13

Expert Comment

by:Shanan212
ID: 38816242
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:agwalsh
ID: 38826699
@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
 
LVL 13

Expert Comment

by:Shanan212
ID: 38827005
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
 

Author Comment

by:agwalsh
ID: 38830406
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
 
LVL 13

Accepted Solution

by:
Shanan212 earned 500 total points
ID: 38831046
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
 

Author Closing Comment

by:agwalsh
ID: 38838797
Thank you :-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now