+ Reply to Thread
Results 1 to 10 of 10

Dynamic Named Range Help - Range Based on Values in Column

  1. #1
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Dynamic Named Range Help - Range Based on Values in Column

    Hi - I am trying to create a dynamic named range that would return the range of cells containing a specific value. Details in example sheet. Thanks!

    DynamicRange.xlsx

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic Named Range Help - Range Based on Values in Column

    Hi,

    One way

    =INDIRECT("A"&MATCH("FY2012-02",Sheet1!$A:$A,0)&":A"&MATCH("FY2012-02",Sheet1!$A:$A))
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Dynamic Named Range Help - Range Based on Values in Column

    Hi Richard - I need the dynamic range to be independent of the fiscal year. The solution needs to look at the month portion only (e.g. "-02"). How would you change the solution? Thanks John

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Dynamic Named Range Help - Range Based on Values in Column

    Try

    =INDEX(Sheet1!A:A,MATCH("*-02",Sheet1!A:A,0)):INDEX(Sheet1!A:A,MATCH("*-02",Sheet1!A:A,0)+COUNTIF(Sheet1!A:A,"*-02")-1)
    Last edited by Cutter; 08-03-2012 at 03:57 PM. Reason: Removed extra brackets

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic Named Range Help - Range Based on Values in Column

    Hi,

    Does that mean that you want to return the range of cells might contain 2011-02, 2012-02,2013-02 etc.?

    In which case if you use the "*-02" modification you'll need to ensure that the data is sorted by month and not, as in your example workbook, by Year_Month.

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Dynamic Named Range Help - Range Based on Values in Column

    Richard
    I took this quote from the sample: "Only data for one FY will be shown (e.g. FY2012 in this example)" as the basis for that

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Dynamic Named Range Help - Range Based on Values in Column

    @cutter
    Indeed, that's the way I read the OP which is why I felt that it didn't matter whether the whole 2012-02 was included, or your shorter wild card + -02. Both return the same result.

    However the second post from filibuster slightly muddies the water when s/he says "I need the dynamic range to be independent of the fiscal year", which implied, (to me at least) that there would be more than one fiscal year but for some reason s/he wanted a range which would cover all the same months whatever the year. I agree that would normally be an odd request but that's the way I interpreted it, hence my Q.

    Regards

  8. #8
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Dynamic Named Range Help - Range Based on Values in Column

    The wildcard is fine. The reason I do not want anything associated with the FY is that the data will all change next year to FY2013. I do not want to go back and update the formulas . Thanks for the help to all - I think I see where I need to go.

  9. #9
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Dynamic Named Range Help - Range Based on Values in Column

    Ah
    I take it to mean that the sheet will be used for various fiscal years but only one year at a time.

    ---------- Post added at 08:19 PM ---------- Previous post was at 08:07 PM ----------

    You're welcome, and thanks for the 'star tap'. Don't forget to mark your thread as SOLVED (assuming it is).

  10. #10
    Registered User
    Join Date
    09-07-2011
    Location
    Sacramento
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: Dynamic Named Range Help - Range Based on Values in Column

    Yes Cutter - well stated. Thanks to both for the help.

+ 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