Finding Named Range Name Based on a cell found within that range.

**Sean Mc** · 10-13-2009, 02:46 PM

Hello All,

I have written vba code which populates a sheet with data.
This data is stored in columns "A" thru "J", and consists of several hundred Rows.
The Macro then breaks this data up into 17 contiguous named ranges - the first row of each of these named ranges is a Title Row with text contained in Column "F". These named Ranges are made up of random numbers of rows.

I have a formula which finds a cell value on this sheet based on a user entry.
The cell which contains this value may be in any one of the 17 Named Ranges, and is an indeterminate number of rows down from my Named Range Title Row.

I would like to display the Text which is contained in my containing Named range Title Row in another cell, based on the location of the found cell.

Example.

Formula one returns a cell location of B98 (into cell M1). B98 is within "Range8". The Title Row of Named Range "Range8" is an unknown number of rows above B98, in Column "F"(Row 1 of "Range8").

I would like another formula (in cell M2) which will return either the Named Range Name("Range8"), or the value in the Title Row of "Range8".

Is this posible?

I have a working method of doing this now using vba code, but am interested in learning if (and how) this could be done with worksheet functions. (Also because my method is rather in-elegant, and could definetly stand to be improved upon)

Thanks in advance for any help,
Sean

**DonkeyOte** · 10-13-2009, 03:03 PM

Sean, I'm sure there are better ways but the below UDF may help ?

Function Hdr(rngCell As Range) As Variant
Dim rngRefers As Range, nm As Name
On Error Resume Next
For Each nm In ThisWorkbook.Names
    Set rngRefers = nm.RefersToRange
    If Err.Number = 0 Then
        If Not Intersect(Range(rngCell.Value), rngRefers) Is Nothing Then
            Hdr = rngRefers.Cells(1, 6).Value
            Exit Function
        End If
    End If
    Set rngRefers = Nothing
Next nm
End Function

Above stored in Module called from cell:

=HDR(M1)

That said if you mix scope of your named ranges it's still open to error I guess... I've also assumed your named ranges commence from Column A.

EDIT: Just noted you said you can already do this in VBA so the above is moot - apologies. Time for me to power down for the day I think...

**DonkeyOte** · 10-13-2009, 03:11 PM

you say the named ranges are contiguous - are the values in col F or are the headers the only values in F ? (ie are they easy to differentiate ?)

**Sean Mc** · 10-13-2009, 04:16 PM

Thanks for the code, - I prefer your code over what I had, so it wasn't a wasted post. :-) Thanks.

The header text is what is in column "F".

The other cell from which my "found" value is pulled, would always be from column "D".

~Sean

**DonkeyOte** · 10-13-2009, 04:21 PM

Yes, I gather the header text is in F but the key is - is there logic by which the header rows can be determined (other than that they are the first row of the named range)...

For sake of illustration of my thinking... let's "assume" F is blank other than for header rows... you could achieve your results using formulae as so

=LOOKUP(REPT("Z",255),F1:INDEX(F:F,ROW(INDIRECT(M1))))

the above would find the last value in F up to an including the row as determined by range reference in M1.

Now in reality the above logic may not be in place but there may be other logical rules by which we can determine as and where the header rows are located.

**Sean Mc** · 10-13-2009, 08:56 PM

Hi again Don,

The only repeating pattern is that column "A" in the Range Header Row is always empty, and it is the only place that cell "A" is empty.
Ill put some thought into what you've given me so far.

Thanks
~ Sean