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
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
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.
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
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
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.
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
@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
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.
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).
Yes Cutter - well stated. Thanks to both for the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks