Solved

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

Posted on 2013-01-24
10
239 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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