Pull out different data depending on date range

I am starting a new project at work and need to calculate usage and increase figures over a 6 week period.

The scenario is: A client support visit takes place. 6 weeks after, I need to show the usage up to that week and calculate the increase during the 6 week period following. My colleagues will also be using this sheet so the data entry needs to be simple.

I will have an input sheet with column headings. Data will be put in columns A-C with the results showing in columns D-E.

A: Client id.
B: Client Name
C: Week No visited
D: the usage on the week visited
E: the increase in usage in the 6 weeks after the visit
F: the percentage increase in the 6 weeks after the visit


I have a separate sheet showing the usage figures in column C. In column A is the client id. I will use a separate sheet for each week and number them as Week1, Week2 etc.

When data is entered in columns A and C, the data is looked up from the appropriate sheets. So, if a visit takes to client No. 20 (A2) and takes place during Week2 (C2), I would enter 20 in the id and 2 in the week number. The lookup will add the client name in B2, the usage at visit week in D2, the usage from sheets Week3-Week8 inclusive in E2, and the percentage increase in F2.

I have put all client numbers and names on a separate sheet to use as lookup as the data may not always be in the same order and I have put the lookup formula in column B to return the client name from the client id field. There is other data I need to show but most will be based on the same formula.
I have an example sheet which I attach.

Ah yes, I see what you are saying. The formula above works well although if there is no data in a cell in column A it returns #REF! What should I put there to leave the cell blank if the corresponding cell in column F is empty?

Column E should return the figure from 6 weeks infront so in the case of Week2, it should return the figure in the same cell for Week8. Column F should return the percentage increase of the 2 figures.

Thanks for your help.

That's Great thanks. I just need to tidy one more bit up and thats to show a blank space for the percentage if A2 is blank. I have tried a couple of things but just get TOO FEW ARGUMENTS. Your help has saved me hours! Thanks again
Simon

OK, I have got it all working. However, what should I change if I want to return the data from column V instead of D in the 'Week' sheets? I have tried to change the array reference from 3 to 22. I tried this: =IFERROR(VLOOKUP($A2,INDIRECT("Week"&C2&"!A:V"),23,0),"") but it doesn't seem to work. I'm not sure where I have gone wrong.

Yes, I saw my error a little later thanks. I have taken your suggestion and will use this in future for ranges.

Another complication is my company year starts on 1st September and I have now been asked to do the same sheet but for a whole year with week1 starting on 1st Sept. My formula for a cell to give me the week number is: =IF(ISBLANK(B3),"",(INT((B3-DATE(YEAR(B3),1,1)-WEEKDAY(B3))/7)+2))

I have changed it to: =IF(ISBLANK(B3),"",(INT((B3-DATE(YEAR(B3),9,1)-WEEKDAY(B3))/7)+2)) which works fine up to December 31st after which it goes horribly wrong. Jan 1st 2012 now shows as week -33 instead of week 19. How can I make it continue to the end of August being week 52?