Solved

Defined named range based on criteria

Posted on 2011-09-24
7
232 Views
Last Modified: 2012-05-12
Hey Experts,

I have a list of persons each related to one single department. What I want is a named range referring to all persons inside a given department. There are just 3 departments

The naming function should be something like this (obviously not working):

nmeAAR={if([DepartmentRange]="AAR",[PersonRange],"")}


- The range should update dynamically after deleting/adding persons
- If it can be done via a function like the above, that would be perfect.

Help, please? :-)

MS Excel 2007 NamingBasedOnCriteria.xlsx
0
Comment
Question by:Raahaugen
  • 4
  • 3
7 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36592844
I think you would need VBA for this.

Add the following code to the sheet module for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim LastR As Long
    Dim Counter As Long
    Dim nam As Name
    Dim dic As Object
    Dim ky As Variant
    Dim TestName As String
    
    If Not Intersect(Target, Me.Range("a:a")) Is Nothing Then
        Application.EnableEvents = False
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = vbTextCompare
        For Each nam In Me.Names
            If LCase(nam.Name) Like "nme*" Then nam.Delete
        Next
        For Each nam In ThisWorkbook.Names
            If LCase(nam.Name) Like "nme*" Then nam.Delete
        Next
        With Me
            LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
            For Counter = 2 To LastR
                TestName = Trim(.Cells(Counter, 1))
                If TestName <> "" Then
                    If dic.Exists(TestName) Then
                        dic.Item(TestName) = dic.Item(TestName) & "," & "'" & Me.Name & "'!" & .Cells(Counter, 2).Address
                    Else
                        dic.Add TestName, "='" & Me.Name & "'!" & .Cells(Counter, 2).Address
                    End If
                End If
            Next
            For Each ky In dic.Keys
                ThisWorkbook.Names.Add "nme" & ky, dic.Item(ky)
            Next
        End With
        Application.EnableEvents = True
        Set dic = Nothing
    End If
    
End Sub

Open in new window


That uses a Dictionary; for more about that, please see:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
0
 

Author Comment

by:Raahaugen
ID: 36593015
Hey

Thanks - seems to work flawless!

Just one add-on:
- Is it possible to sort the range of the names alphabetically?

/Raahaugen
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36593355
>>- Is it possible to sort the range of the names alphabetically?

Probably, but you'll need smeone else to do that.  You might want to pursue it in a new question :)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Raahaugen
ID: 36596087
I can't seem to make it work in office 2010. Why is that?

The workbook are pasted.

I know the names are created, but if I update the list, the names are not updated

/Raahaugen
Test.xlsm
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36596841
Please explain.  You asked about updating the Names, and that is exactly what I did.
0
 

Author Comment

by:Raahaugen
ID: 36597637
The problem I figured was, it works in excel 2007 but not in 2010?

The names are not created - the script either not running or not running right in 2010
0
 

Author Closing Comment

by:Raahaugen
ID: 36598522
Works as asked.

I'm not sure the solution works in 2010, but there seems to be no problem in 2007, which was what I asked for.

Thanks a lot

Good day
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Rename multiple files in folder from list in table/excel. 4 36
Msgbox tickler 10 23
If help 9 48
Data Copy 4 23
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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