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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|