Lookup and Return Data Between 2 Dates

[samsnb]

Hi,

I need to look into a table and return only the data between two dates. There is one master input sheet and 12 monthly summaries - the 12 monthly summaries need to extract the data between 2 dates. I have used the following function to count the number of entries valid for that particular month:

=SUMPRODUCT((Table91619222528313437465[Date]>='Apr 12'!$B$4)*(Table91619222528313437465[Date]<='Apr 12'!$C$4))

With B4 being - 1/4/12
C4 - 31/4/1

I am then using the following formula to extract only the relevant lines for that particular month:

=IF(ROWS(B$9:B9)>$C$5,"",INDEX(Table91619222528313437465[Date],SMALL(IF(('Total sheet'!$B$9:$B$299>='Apr 12'!$B$4)*(('Total sheet'!$B$9:$B$299<='Apr 12'!$C$4)),ROW(Table91619222528313437465[Date])-ROW(Table91619222528313437465[[#This Row],[Date]])+1),ROWS(B$9:B9))))

The first row works fine however the initial problem is that the first formula isn't working correctly - it is returning the total number of rows in the table. I think it is the second half that is the issue the <= part.

The second issue is the second formula is functioning correctly across the first row but when copied down (in the monthly summaries) it is returning #NUM!. The reason why I am not sure.

I took the idea to do it this way from the following Youtube tutorial but I cannot seem to emulate his success:

http://www.youtube.com/watch?v=9jmNE...A7644FE57C97F4

Any help would be greatly appreciated.

Thanks

Sam

[Fotis1991]

Do you confirm yhe formula as an ARRAY formula?

[samsnb]

Hi, Thanks for your reply - sorry I don't understand. How do I go about confirming it is an array formula?

Thanks

Sam

[Fotis1991]

Did you try confiring this using CONTROL+SHIFT+ENTER ??

[samsnb]

Hi, Yes I did but it didn't compute. However, I think I have solved the issue myself now. Thanks, Sam.

[samsnb]

Sorry, I have solved the first issue but the second formula after the first row is giving me #NUM! still

[Fotis1991]

Upload a small sample workbook pls.

[samsnb]

Please find it attached, Thank you for your help.Report Template Internet Upload.xlsx

[Fotis1991]

Sam. I asked you twice if you confirmed this as an ARRAY formula..

[samsnb]

I did, but whilst messing around it must have gone back to normal. The columns D and E are both array - how can I get it to pull the correct column from the main sheet?

Thanks for your help so far it is greatly appreciated.

[Fotis1991]

Did you see the attached sample worksheet?

[samsnb]

Hi, Yes I downloaded it - but it is only pulling across the dates for all 4 columns?

[samsnb]

Hi, Problem solved - thank you.

[Fotis1991]

................................

[samsnb]

Hi, I am input all that into the sheet but now I have no ide why some of the months are now getting mixed up. Please see attached the sheet - I am completely at a loss with it now.

Thanks

SamReport Template Internet Upload.xlsx

[Fotis1991]

I don't know too. Works for me...What i did is this.

I converted the table to range. I puted formulas there and then i converted again to table.

As you can see in the attached sample sheet, works ok.

Good luck.