Solved

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

Posted on 2013-01-24
10
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 

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
 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

734 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