Pulling a Range of Dates and their Corresponding Values

Hi,

Welcome to the Forum.

Your requirement can be fulfilled with sheet formulas. Kindly upload a workbook with sample data (do remove sensitive information).

Use a pivot table for that kind of problem.

Does this help? Copy Sheet2!B3:C3 down for the range of dates you want.
DirecterReturnsHelp.xlsm

Do you need the detail of the date ranges, or just things like the totals or averages. You said you needed to further analyse the data within those ranges and there may be a better way to do that. SUMIFS, COUNTIFS and AVERAGEIFS for instance can use multiple criteria such as date range and manager's name. OFFSET function can also be used to specify a range to be further analysed.

Then i'm interested how the result should be.

You didn't add this in your file.

In the example you uploaded try something like:

Formula:

Please Login or Register  to view this content.

to get the range to further analyse.

I made a slight edit to post 10 so use the current post, not the email notification you received from the forum.

Yes. It would just need something like: OFFSET('[Path and filename]Sheetname'!$C$6,MATCH([Start date of range],'[Path and filename]Sheetname'!$C$7:$C$22,0),MATCH([Director name],'[Path and filename]Sheetname'!$D$6:$F$6,0),COUNTIFS('[Path and filename]Sheetname'!$C$7:$C$22,">="&[Start date of range],'[Path and filename]Sheetname'!$C$7:$C$22,"<="&[End date of range]),1)

I'm heading out for a bit, but if you upload some sample files I'll have a look when I get back.

See the attached file. In a separate book to the sample you uploaded I summed the data from your upload for the range 13/2/12 to 20/2/12 for Frank
Book1.xlsx
You will need to of course change the references to match your data and file locations.

With an pivot table (as suggested)

See tab Oeldere

DirecterReturnsHelpUpdated.xlsmI uploaded a file with some of the calculations that I need to do. Hopefully this will give you a better sense of what I'm trying to do.

I have a set group of calculations that I need to do for any given range of data. The only thing that needs to change is the director data and the dates.

All of the data is located on a separate worksheet. Its similar to the sample data I provided, except there are a lot more people and a lot more dates. Currently, if I want to make a new comparison, which I do a lot, I have to copy and past the range I want into a new worksheet. I then have to create a table like the one in the worksheet provided and redo all the calculations. It gets pretty tedious and I feel like there is a way to make this more automated.

Ideally, all i would need to change would be the director and the dates, and then all of the calculations would be done so I dont have to redo them every time.

Sorry if I have been unclear, I'm just trying to figure out the best way to do this.

Thanks

Try these, but with the parts in red replaced with the references to your separate workbook (with both workbooks open select the part of the formula in the cell and then select the relevant part in the other workbook).
In L11:

Formula:

Please Login or Register  to view this content.

In L12:

Formula:

Please Login or Register  to view this content.

In L13:

Formula:

Please Login or Register  to view this content.

In L14:

Formula:

Please Login or Register  to view this content.

In L15:

Formula:

Please Login or Register  to view this content.

In L16:

Formula:

Please Login or Register  to view this content.

In L17:

Formula:

Please Login or Register  to view this content.

In L18:

Formula:

Please Login or Register  to view this content.

See attached:DirecterReturnsHelpUpdated.xlsm

I plugged in the references from my sheets and the numbers looked good. However, I checked my my numbers against the old way that I did it and they are a little off. I can't seem to figure out whats going wrong. Any ideas?

Maybe something to do with the -6? I'm a little confused as to what that actually means.

I can think of two likely things to look at. The -6 is one of them, The MATCH function returns the position (in this case the number of rows down column C) of the thing you're looking up (in this case the start of your date range). As your example had 6 rows before the data started at the top of the column I needed to put the -6 in the formula. You will need to adjust that number to suit your data. Count any non data cells in the date column, including blank cells, headings, etc.

The other option is the $K$9-$K$8+1 towards the end. Your sample data showed no gaps in the dates so I assumed that would continue for your full data source. The height of the range to be analysed needs to be the number of rows between the from date and the to date, which if there are no gaps is simply the number of days between the two, plus 1 to make it inclusive of both the start and end dates. If your source data is not continuous there are ways to fix that, so let me know.

In the first match function replace the $K$8 with DATE(YEAR(TODAY(),1,1) and replace the $K$9-$K$8+1 towards the end with TODAY()-DATE(YEAR(TODAY(),1,1)+1

Sorry. No, your options are not the right ones. It should be DATE(YEAR(TODAY()),1,1)

Upload a sample of your ACTUAL data and I'll have a look. I'm heading to work now so it may be a while before I can have a look at it.

Yeah, sorry, I should have said your ACTUAL data layout. Didn't mean to ask you to upload sensitive data.

A couple of questions to clarify how to proceed.

What form will the from and to dates take? Ie. will it be the first of a month to a last of a month? Will it be the last of a month to the last of a month since that is the way the dates in the data are? Could it be any date of a month to any date of a month?

If it is any date in a month do you include that month? Eg. if it was from 15 June 2012 to 15 May 2013 is your starting point the 30 June 2012 data or do you start with the 31 May 2012 data. Do you end with the 31 May 2013 data or do you finish with the 30 April 2013 data?

Cheers

YTD function I don't think will give you what you want. For instance as at today (18 June 2014) your formula will give you 30 June 2014 (or 41820) - 31 Jan 2014 (or 41670) +1, or 151 rows of data, when you really want 6 rows of data (Jan 14 - Jun 14). You could try COUNTIFS(A:A,">="&EOMONTH(DATE(YEAR(TODAY()),1,1),0),A:A,"<="&EOMONTH(TODAY(), 0))). This assumes the dates are in column A, and I prefer to use TODAY() rather than NOW() when times are irrelevant.

Yes, 151 rows is the right number for daily data.