Can I make a formula that always uses the latest wksht in Book2?

[grateful]

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

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.

 

[Glenn]

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

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.

Have you considered naming the current sheet "CURRENT" and then changing the
name to "December 4, 2008" when it is no longer current?

 

[grateful]

Hi, Sorry it took me so long to respond.

Yes, that worked THANK YOU.

I had tried something similar quite awhile ago, but I must have been doing
something wrong. Since that time...I've learned a lot. And yes, it did work.

Thank you!