Refer to other tabs in formula

[Gary T]

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?

 

[David Biddulph]

INDIRECT is what you need.

Remember to concatenate whatever text string you need in the argument of the
INDIRECT function. Putting it another way, decide what your formula looks
like when you have specfied the cells and worksheets directly. [Remember to
get the terminology right. You talked in your message about "May 2008 and
April 2008 workbooks" but I think you meant worksheets.] Make sure that the
formula has valid syntax and works correctly. Then you can use a
concatenation formula to produce a text string that looks like the worksheet
and cell address in your formula. [If you like, try that concatenation
formula out with just an = at the beginning to see that you've got the right
text string as your formula result. Then put that concatenation formula
inside your INDIRECT function and use that instead of the worksheet and cell
reference in your formula.

If you are still struggling, tell us what INDIRECT formula you tried and
what normal formula reference you were trying to replace..

 

[Glenn]

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?

=IF(INDIRECT("'"&TEXT(A1-1,"mmmm")&" "&TEXT(A1-1,"yyyy")&"'!I6")=AB1
,INDIRECT("'"&TEXT(A1-1,"mmmm")&" "&TEXT(A1-1,"yyyy")&"'!E6"),"")