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