A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Refer to other tabs in formula



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2008, 06:03 PM posted to microsoft.public.excel.worksheet.functions
Gary T
external usenet poster
 
Posts: 26
Default Refer to other tabs in formula

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?

  #2  
Old September 23rd, 2008, 07:11 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default Refer to other tabs in formula

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..
--
David Biddulph

"Gary T" wrote in message
...
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?



  #3  
Old September 23rd, 2008, 07:21 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default Refer to other tabs in formula

Gary T wrote:
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"),"")
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:47 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.