Solved

# Defined named range based on criteria

Posted on 2011-09-24
231 Views
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.

MS Excel 2007 NamingBasedOnCriteria.xlsx
0
Question by:Raahaugen
• 4
• 3

LVL 92

Accepted Solution

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
End If
End If
Next
For Each ky In dic.Keys
Next
End With
Application.EnableEvents = True
Set dic = Nothing
End If

End Sub
``````

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

Author Comment

ID: 36593015
Hey

Thanks - seems to work flawless!

- Is it possible to sort the range of the names alphabetically?

/Raahaugen
0

LVL 92

Expert Comment

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

Author Comment

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

ID: 36596841
0

Author Comment

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

ID: 36598522

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …