Another useful little snippet from Eng-tips.
There was a question regarding how to list all the range names applied to a specified cell. Forum members electricpete and MintJulep came up with a short macro to do the job, which I have adapted to suit my personal tastes, as a User Defined Function (UDF), returning a list of names and addresses as an array function. Here is the code:
Function FindNamedRanges(mycell As Range) Dim ThisName As Name, NameList() As String, i As Long, NumRows As Long NumRows = Application.Caller.Rows.Count ReDim NameList(1 To NumRows, 1 To 2) i = 1 For Each ThisName In Names If ThisName.RefersToRange.Parent Is mycell.Parent Then If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then NameList(i, 1) = ThisName.Name NameList(i, 2) = ThisName.RefersTo i = i + 1 If i > NumRows Then Exit For End If End If Next ThisName FindNamedRanges = NameList End Function
The function returns a two column array, with the range names in Column 1 and the addresses they apply to in Column 2. To display the full array:
- Enter the function.
- Select the output range, with the function cell in the top-left corner
- Press F2 (edit)
- Press ctrl-shift enter
An alternative approach, avoiding the use of macros, is to simply list all the rangenames on a spreadsheet, then sort by address. It turns out that the poster of the original question prefered this approach. I like the UDF myself, but “disputandum non est de gustibus”, as the tortoise said to the warrior.
Newton Excel Bach, not (just) an Excel Blog 
Great website. Alot of good information. I will be looking at this often to learn more.
I am wondering if you can help me with a challenge that I am having.
Using “=countifs “returns the number of times that logical conditions are met for a set of criteria within a range. Once this is done is there a formula or a means by which you can find the cell adress of this occurence? I need to be able to find the address to then find a coresponding value in another cell 2 collumns over.
LikeLike
Hi Jace, thanks for the comments.
There are two parts to your question:
– matching rows after the first match
– matching with multiple criteria
The first part can be done with a combination of Match and Offset:
=MATCH(B7,OFFSET($A$20,C6,0,$D$3-C6,1),0)+C6
This formula is in cell C7, the value to be matched is in B7, the data range starts in A20, C6 is empty or contains zero, and D3 contains the number of rows in the data range. If you copy that down (with the match value in column B copied down as well), the Offset formula adjusts the range, and the formula should return the row for each matching cell.
I posted a UDF to do the second part some time ago (https://newtonexcelbach.wordpress.com/2009/02/11/multiple-match-function/)
I’m going to modify that function to also return an array with each matching row number, rather than just the first match.
Should appear in the next day or two.
Doug
LikeLike