Referencing Weekly data from large raw data set

**freud1** · 03-08-2009, 05:14 PM

Hi Guys,

I already have had help to produce a formula to reference a large amount of data and pull one month at a time:

Please Login or Register  to view this content.

I now need to adapt this so it will pull weekly data on another sheet.

I have attached the workbook beause i find it hard to explain what i mean,

I basicly want another sheet that i can choose week 1 - 52 and data from seven days will be shown.

Thanks for all your help!

**JBeaucaire** · 03-08-2009, 05:41 PM

This is an INDEX / MATCH procedure. Your data sheet starts on a Tuesday, so that is how my formula is reading the "first day of the week". You can adjust the starting value in E3 to A3 or A4 or whatever to set the actual "first day" for the calculations.

**mrice** · 03-08-2009, 05:52 PM

Have a look at the attachment.

I added some helper columns to the first sheet to determine week number and week day and then concatentated these into a unique string.

**JBeaucaire** · 03-08-2009, 05:53 PM

Here it is a again with similar methodology applied to the Monthly Sheet, also made the erroneous dates like February 30th/31st no longer appear.

**freud1** · 03-08-2009, 05:54 PM

Hey thanks for your help guys!!!!

well and truely saved me like 10 hours of playing around!

**freud1** · 03-08-2009, 06:27 PM

having a bit of trouble adapting this to my spreadsheet guys, I have used MRICE's method as it displays the week from monday - sunday, i just keep getting the same data no matter which week I am on,

any ideas?

**JBeaucaire** · 03-08-2009, 07:18 PM

My standard INDEX/MATCH will do the same if you adjust the starting cell in E3 as I pointed out. You can also insert the missing monday at the top of your data sheet to get the same result...12/31/2007.

Like so.