auto populate sheet references that skip cells

Hi,

I am trying to set up a summary sheet that references two other sheets that are formatted exactly the same. one sheet is for forecasting and the other is actual data.

I have set it up so that the date travels across the page for an entire year. What I would like to do is have a summary sheet that lists two colomns side by side showing forecast and actual for each sunday in the year progressing across the page. I have about 15 rows of data I want to compare so don't want to manual refernece 52 x 2 x 2 cells (thats alot!), but I cannot work out how to autopopulate each alternating colomn of forecast data from the forecast sheet and actual data from the actual sheet. Can anyone help me please with a formula that i would be able to use that would auto populate for each sunday (weekending) across the colomns by clicking and dragging.

Cheers for any tips

Groovy

Hi

can you attach an example file so we can see your structure without having to make assumptions.

rylo

here is my sheet

In Actual and Forecast sheets B column has got date 1-7-2012, and in Weekly summary sheet B&C columns has date 1-6-2012. It is confusing. Pl clarify.

oops, the summary sheet is supposed to start with july not june (just a mistake)

groovy

Pl see attached file.
Formulas are in Two Sheets .Which is convenient you can use.
Thanks.

Wow! you are definately an excel GURU. A thousand thanks.

Groovy

Thanks for compliments.
Pl mark the thread SOLVED.

@ groovy

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.