Referecing Worksheet names that are dates in a formula

[David McLean]

Hi There,

I've got a tricky one here, and I'm not sure if anything can be done about
it. Here's the situation...

I've got a workbook that I use to track the number of proposals my company
writes each month. There is one tab for each month, labelled with the format
mmmm yyyy (i.e. November 2009, December 2009, January 2010, etc.) and a
couple of summary sheets that reference data from each of the months.

I need to start a new summary sheet that only looks at the last 6 months,
excluding the current month, (so during March 2010, I need it to draw data
from September 2009 to February 2010 tabs. The data that i need is in the
same cell on each sheet.

Is there a formula that I can use that would automate this?

If anyone has any advice, I would very much appreciate it.

 

[T. Valko]

I need it to draw data from September 2009 to

February 2010 tabs. The data that i need is in the
same cell on each sheet.

Try this...

You want to link to cell A1 on each of those sheets and have the formulas
update automatically when each new month starts.

Enter this formula in cell A2 on your summary sheet and copy down as needed:

=INDIRECT("'"&TEXT(DATE(YEAR(NOW()),MONTH(NOW())-ROWS(A$2:A2),1),"mmmm
yyyy")&"'!A1")

A2 will link to February 2010
A3 will link to January 2010
A4 will link to December 2009
A5 will link to November 2009
etc
etc

 

[T. Valko]

Just in case line wrap screws up the formula here it is in chunks. Just make
sure you enter it all on one line in the file.

=INDIRECT("'"&TEXT(DATE(YEAR(NOW()),
MONTH(NOW())-ROWS(A$2:A2),1),
"mmmm yyyy")&"'!A1")