+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    10-13-2009
    Location
    Providence, RI, USA
    MS-Off Ver
    Excel 2002
    Posts
    3

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

    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

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

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

    Please Login or Register  to view this content.
    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...

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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 ?)

  4. #4
    Registered User
    Join Date
    10-13-2009
    Location
    Providence, RI, USA
    MS-Off Ver
    Excel 2002
    Posts
    3

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

    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

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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.

  6. #6
    Registered User
    Join Date
    10-13-2009
    Location
    Providence, RI, USA
    MS-Off Ver
    Excel 2002
    Posts
    3

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

    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

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

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

    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.
    so on that basis perhaps

    Please Login or Register  to view this content.
    It would be better to adjust M1 such that you don't need to use INDIRECT but without knowing how that's determined it's hard to offer further insight at this stage.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1