Hi, If you can help me I would be so "grateful" :o)

I want to make a workbook named Orange with 4 worksheets:

Worksheet 1: Red
Worksheet 2: Blue
Worksheet 3: Green
Worksheet 4: Yellow

I want worksheet Red to go to book "Red" and using the latest worksheet put
all the figures in column C from book "Red" into Column A of worksheet "Red"
in Book "Orange"; and the figures in column N from book "Red" into Column B
of worksheet "Red" in Book "Orange";

The reason being is every week, I make a new report in Red, Blue, Green, and
Yellow.
For example Red will have worksheets that look like this at the bottom:

November 29, 2008 / November 20, 2008 / November 16, 2008 / November 9, 2008

and next week the worksheet on the furthest left it will say December 4, 2008

In book Orange I want just the information necessary pertaining to the
latest week with out always changing the name of the worksheet in the formula.

I was hoping there was a formula that could automatically identify the
worksheet on the furthest left of another book and change the figures
accordingly.

I don't know if it's possible, but if you have any answers I would be
extremely grateful.

Thank you so much in advance.