?
Solved

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

Posted on 2013-01-24
10
Medium Priority
?
240 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

801 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