I have a series of tabs within a workbook labelled "April 2008", "May 2008",
"June 2008", and so on.

In cell A1 of each tab, I have entered the start date of each month - e.g.
In cell A1 of the "April 2008" tab I have entered 01-Apr-08 (in date format).

I would like to generate a formula that refers to cells within the two tabs
from the previous month - e.g. In the June 2008 tab, I would like to enter
this formula in cell A6 (and similar formulae in other cells) that looks at
cells from both the May 2008 and April 2008 workbooks. The formula in cell
A6 of the July 2008 tab would look at the same cells from the June 2008 and
May 2008 tabs, and the formula in A6 of the August 2008 tab would look at the
same cells from the July 2008 and June 2008 tabs.

I need this workbook to be used throughout the next 30 years and (since I
might not always be around!) would like to develop a template and leave
instructions so that when a user needs to prepare the e.g. September 2008
tab, they would select €œmove or copy sheet€� (i.e. copy the template) by
right-clicking the €œTemplate€� tab, copying the template, and the user can
enter e.g. September 2008 into cell A1 of the Template, rename the worksheet
September 2008, and this would be all they needed to do to allow the formula
in the September 2008 workbook to know to look at the cells in the August
2008 and July 2008 workbooks

I have tried the following formula: =IF('TEXT(A1-1,"mmmm")&"
"&TEXT(A1-1,"yyyy")'!$I6=AB$1,'TEXT(A1-1,"mmmm")&"
"&TEXT(A1-1,"yyyy")'!$E6,"")

The aim of this is to always return the contents of cell E6 of the previous
month's tab, if the contents of cell I6 of the previous month's tab equals
the contents of AB1 in the current month's tab, otherwise return a blank €œ€�.

However, I'm currently always getting a #REF! error as Excel doesn't seem to
like me using a formula that generates text when it is expecting a reference.
I've tried playing around with INDIRECT but that doesn't seem to work either.

Any ideas?