Dynamic named range based on a start date and end date

I am hoping someone can help me with a dynamic named range.

I have a datasheet with a column - (A) of dates.

How would I get a named range based on an opening date and closing date of column A in the datasheet, there is one other complication in that there can be several instances of the same date, in that case I would need the earliest start date and the latest end date.

e.g. (The example shows dates at the beginning of the month only as an example)

Column A

01/05/2011
01/05/2011
01/06/2011
01/07/2011
01/08/2011
01/09/2011
....
30/04/2012
30/04/2012

Therefore I would need a name range say from 01/05/11 to 30/04/12 as say Year 1, Year 2 would be to 30/04/13 etc.. (these start and closing dates will change)

Can anyone help?

The OFFSET function is ideal for this. You going to make me create a workbook that doesn't really match your layout and will cumbersome to adapt? Or how about you provide one showing the sample data, where these Begin/End dates will occur and where the drop down needs to exist?

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

JBeaucaire

Many thanks for your response.

I have attached an example workbook as requested

Example 2.xlsx

Many thanks

Originally Posted by JBeaucaire

how about you provide one showing the sample data, where these Begin/End dates will occur and where the drop down needs to exist?

So, how about those last time items? If the named range is going to exist it needs to get the data from somewhere. We have column A, now what cell will it derive the start date? The End date?

Sorry I forgot to mention this.

In the example we can use H1 for the start date and H2 for the end date.

Many thanks for your time.

Use this for your YEAR1 Named Range:

Year1: =INDEX(Sheet1!$A:$A, MATCH(Sheet1!$H$1, Sheet1!$A:$A, 0)):INDEX(Sheet1!$A:$A, MATCH(Sheet1!$H$2, Sheet1!$A:$A, 0)+COUNTIF(Sheet1!$A:$A, Sheet1!$H$2)-1)

http://screencast.com/t/KdDYa3x54QFm


Then an example formula to perhaps SUM all the amounts for Year1:

=SUM(OFFSET(Year1,,4,,))

Jerry

Many thanks, unfortunately my actual file needs a variance to this which I overlooked.

I thought that once the date range was defined I could extend this to include to col Z, but I don't understand enough of what you have done to achieve this!

I am trying to enter this named range (Year1) into another formula which needs referencing from col A to col Z, not just col A -(the date column) but it is the data between the dates that is important and needs to be named.

Does this make sense?

---------- Post added 08-05-2012 at 12:21 AM ---------- Previous post was 08-04-2012 at 11:50 PM ----------

I don't know if I am supposed to do this but I have another thread that is not being answered which relates to this thread.

My actual formula is:

Please Login or Register  to view this content.

The named range "year1" is to replace "datasheet!$A$1:$Z$10000". I have proved that if I put A1 as the opening date and say Z25 as the closing date then the formula works correctly and extracts the correct info for the correct period.

I hope this makes sense?

No, it doesn't make sense at first blush. I expected a sample sheet that manually demonstrated what you were trying to automate, all you provided was a column of dates. Show me the goal line so we can get this all right in one try instead of baby-stepping.

Jerry

My apologies, my attempt to simplify things backfired on me.

I have attached a revised file showing what I would like to do, any help would be greatly appreciated.

Example 3.xlsx

Many thanks

I still think your example is slightly lacking. You indicate a desire to have "all the columns on the Datasheet from A:Z that are used", but you still only present a single "set" of data on that sheet.

But let me interject here, stop trying to do that. The whole point of a "database sheet" and a separate "reporting sheet" is supposed to be simplicity. That means you stick with standard practices for a database sheet and put all the data in a single ongoing data table going down as far as needed, millions of rows if necessary. One Table. Not side-by-side tables, that's not standard and introduces massive amount of plumbing additions. No, keep the table standard, then do all the fancy stuff on your report sheet.


Without needing to fiddle with names ranges or any of that, here's a more basic demonstration on your data. With a single database and single report table, this version shows how you can enter any dates in the START/END fields on the report and it will present the rows from the database that match that date range. Any range. So you can enter the year1 dates to see that data, enter year2 to see that. No fuss, no muss.

Jerry

Thank you for your workbook and workings.

I think we are at cross purposes in what I need, you have shown me how to now select all the transactions within a date period, but I need selected transactions within that date period.

I have attached a revised file showing my amendments if you would be kind enough to have a look and hopefully make some suggestions.

Many thanks

ReportFromDatabase (1).xlsx

Heh, I'm a fan of making things easy to use. Now that you have a nice easy to use REPORT sheet, we can keep the lookup formulas neat and clean by adding one little "key" column to the actual DATA. I've inserted a column A because your dates exist on every row, you can quickly copy the formula in A4 down the whole dataset. This formula creates an INDEX based on the category and dates you've selected on the report sheet.

Now, the REPORT sheet needs only grab the first instance of each unique number in that index to instantly show just the category/dates you've outlined.

Jerry

You are a genius!

Now what if i wanted to show on the report sheet, these reports with multiple years, i.e. say - year1 1st May 2011 to 30th April 2012, year2 1st May 2012 to 30th April 2013, etc, side by side. So when I select an expense heading I can see these entries year on year?

I don't want much do I!

Side by side? I know people like pretty reports, but your form will show years1 and 2 by simply putting in that date range... May1,2011 >> April30,2013.

If you really want, feel the need to do side by side (?), then you can duplicates the method I've shown by adding a second key column and a second table, edit the formulas to use the dates in the second section.

I wouldn't do any of that. The single table format is friendly and easy to use. If you really needed an occasional side by side, I'd run the report for the full range and copy teh results to another sheet or email and layout the info for printing....

...no, I was right the first time. I wouldn't do any of that.

I'm going to beg off now.