Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
How can I reference a worksheet tab in a formula, so that it always refers to
the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
To get, in a formula, the current sheet name, you can use the
following expression: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) In a separate area in your workbook you can have a list of all worksheets, in the order in which they appear in Excel (and you want to use them). Say this is in sheet List and occupies cells A1:A10. You can define a name (InsertNameDefine...) for this range, say shList. Now, instead of pointing to the data of a previous sheet with a formula such as: ='Sheet 1'!A1 You can use INDIRECT in the following manner: =INDIRECT("'"&INDEX(shList,MATCH(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1)) +1,255),shList,0)-1)&"'!A1") You can then copy this formula in any other sheet and it will refer to the previous sheet. HTH Kostis Vezerides On Jul 5, 6:20 pm, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
Susan
Function PrevSheet(rg As Range) 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. Application.Volatile n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function Gord Dibben MS Excel MVP On Thu, 5 Jul 2007 08:20:02 -0700, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
Is that VB Code at the bottom of your message? Do I just copy and paste that
into VB? Do I need to reference my worksheet names somewhere? They are called Pay Period 6, Pay Period 7, Pay Period 8, etc. I tried putting PrevSheet(F4) into my second worksheet, but it didn't populate with the previous worksheet's data. Susan "Gord Dibben" wrote: Susan Function PrevSheet(rg As Range) 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. Application.Volatile n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function Gord Dibben MS Excel MVP On Thu, 5 Jul 2007 08:20:02 -0700, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
This is a User Defined Function and no, you don't need to reference your sheet
names. Have your workbook open. Hit Alt + F11 to open the Visual Basic Editor. CTRL + r to open the Project Explorer. Right-click on your workbook/project and InsertModule Paste the code into that module. Then Alt + q to return to your worksheet. In Pay Period 7 sheet F4 enter =PrevSheet(F4) That will give you Pay Sheet 6 F4 contents. A quick way to enter on all sheets at once is to select all but first sheet then in F4 of active sheet enter the above formula which will be entered in all grouped sheets. Gord On Thu, 5 Jul 2007 10:06:03 -0700, Susan wrote: Is that VB Code at the bottom of your message? Do I just copy and paste that into VB? Do I need to reference my worksheet names somewhere? They are called Pay Period 6, Pay Period 7, Pay Period 8, etc. I tried putting PrevSheet(F4) into my second worksheet, but it didn't populate with the previous worksheet's data. Susan "Gord Dibben" wrote: Susan Function PrevSheet(rg As Range) 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. Application.Volatile n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function Gord Dibben MS Excel MVP On Thu, 5 Jul 2007 08:20:02 -0700, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
"Susan" wrote...
How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Yet another approach, this time involving XLM functions (so dangerous in Excel 2000 and prior). Define the name _WSLST referring to the formula =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.DOCUMENT(88)&"]","") Then select a blank row in some worksheet, name it WSLST and enter the array formula =_WSLST in it. Also define the name _WBWS referring to the formula =CELL("Filename",!$1:$65536) and the name WSNAME referring to the formula =MID(_WBWS,FIND("]",_WBWS)+1,32) With this setup you can retrieve the name of the previous worksheet with =INDEX(WSLST,MATCH(WSNAME,WSLST,0)-1) [I'm not sure whether using WSLST in place of _WSLST fixes the problem in Excel 2000 and prior in which copying a range containing formulas that refers to names that directly call XLM functions and pasting into other worksheets crashes Excel. Maybe referring to a range instead prevents this. Anyone else what to check?] |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
Should "filename" be the name of my workbook? And is A1 referring to the
shList or to the cell that I'm entering this formula into? When I enter the formula as given, it returns #VALUE! "vezerid" wrote: To get, in a formula, the current sheet name, you can use the following expression: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) In a separate area in your workbook you can have a list of all worksheets, in the order in which they appear in Excel (and you want to use them). Say this is in sheet List and occupies cells A1:A10. You can define a name (InsertNameDefine...) for this range, say shList. Now, instead of pointing to the data of a previous sheet with a formula such as: ='Sheet 1'!A1 You can use INDIRECT in the following manner: =INDIRECT("'"&INDEX(shList,MATCH(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1)) +1,255),shList,0)-1)&"'!A1") You can then copy this formula in any other sheet and it will refer to the previous sheet. HTH Kostis Vezerides On Jul 5, 6:20 pm, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
When I entered the formula into the spreadsheet, it returned me to VBE and
gave me the message, "Invalid Procedure" and the word "Application" was highlighted in blue. "Gord Dibben" wrote: This is a User Defined Function and no, you don't need to reference your sheet names. Have your workbook open. Hit Alt + F11 to open the Visual Basic Editor. CTRL + r to open the Project Explorer. Right-click on your workbook/project and InsertModule Paste the code into that module. Then Alt + q to return to your worksheet. In Pay Period 7 sheet F4 enter =PrevSheet(F4) That will give you Pay Sheet 6 F4 contents. A quick way to enter on all sheets at once is to select all but first sheet then in F4 of active sheet enter the above formula which will be entered in all grouped sheets. Gord On Thu, 5 Jul 2007 10:06:03 -0700, Susan wrote: Is that VB Code at the bottom of your message? Do I just copy and paste that into VB? Do I need to reference my worksheet names somewhere? They are called Pay Period 6, Pay Period 7, Pay Period 8, etc. I tried putting PrevSheet(F4) into my second worksheet, but it didn't populate with the previous worksheet's data. Susan "Gord Dibben" wrote: Susan Function PrevSheet(rg As Range) 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. Application.Volatile n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function Gord Dibben MS Excel MVP On Thu, 5 Jul 2007 08:20:02 -0700, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
Leave filename as it is, A1 is just there as a cell because the function
needs a cell reference -- Regards, Peo Sjoblom "Susan" wrote in message ... Should "filename" be the name of my workbook? And is A1 referring to the shList or to the cell that I'm entering this formula into? When I enter the formula as given, it returns #VALUE! "vezerid" wrote: To get, in a formula, the current sheet name, you can use the following expression: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) In a separate area in your workbook you can have a list of all worksheets, in the order in which they appear in Excel (and you want to use them). Say this is in sheet List and occupies cells A1:A10. You can define a name (InsertNameDefine...) for this range, say shList. Now, instead of pointing to the data of a previous sheet with a formula such as: ='Sheet 1'!A1 You can use INDIRECT in the following manner: =INDIRECT("'"&INDEX(shList,MATCH(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1)) +1,255),shList,0)-1)&"'!A1") You can then copy this formula in any other sheet and it will refer to the previous sheet. HTH Kostis Vezerides On Jul 5, 6:20 pm, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
It is now returning NAME.
"Peo Sjoblom" wrote: Leave filename as it is, A1 is just there as a cell because the function needs a cell reference -- Regards, Peo Sjoblom "Susan" wrote in message ... Should "filename" be the name of my workbook? And is A1 referring to the shList or to the cell that I'm entering this formula into? When I enter the formula as given, it returns #VALUE! "vezerid" wrote: To get, in a formula, the current sheet name, you can use the following expression: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) In a separate area in your workbook you can have a list of all worksheets, in the order in which they appear in Excel (and you want to use them). Say this is in sheet List and occupies cells A1:A10. You can define a name (InsertNameDefine...) for this range, say shList. Now, instead of pointing to the data of a previous sheet with a formula such as: ='Sheet 1'!A1 You can use INDIRECT in the following manner: =INDIRECT("'"&INDEX(shList,MATCH(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1)) +1,255),shList,0)-1)&"'!A1") You can then copy this formula in any other sheet and it will refer to the previous sheet. HTH Kostis Vezerides On Jul 5, 6:20 pm, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
Susan,
If you are confused about the syntax of an Excel function, such as CELL, then the best bet is to type its name into Excel's help. It'll tell you about the syntax, give examples, & usually tell you about related functions through its "See also" link. -- David Biddulph "Susan" wrote in message ... Should "filename" be the name of my workbook? And is A1 referring to the shList or to the cell that I'm entering this formula into? When I enter the formula as given, it returns #VALUE! "vezerid" wrote: To get, in a formula, the current sheet name, you can use the following expression: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) In a separate area in your workbook you can have a list of all worksheets, in the order in which they appear in Excel (and you want to use them). Say this is in sheet List and occupies cells A1:A10. You can define a name (InsertNameDefine...) for this range, say shList. Now, instead of pointing to the data of a previous sheet with a formula such as: ='Sheet 1'!A1 You can use INDIRECT in the following manner: =INDIRECT("'"&INDEX(shList,MATCH(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1)) +1,255),shList,0)-1)&"'!A1") You can then copy this formula in any other sheet and it will refer to the previous sheet. HTH Kostis Vezerides On Jul 5, 6:20 pm, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference a Worksheet Tab in Formula
Post the exact formula that returns the #NAME! error, you must have a typo
somewhere -- Regards, Peo Sjoblom "Susan" wrote in message ... It is now returning NAME. "Peo Sjoblom" wrote: Leave filename as it is, A1 is just there as a cell because the function needs a cell reference -- Regards, Peo Sjoblom "Susan" wrote in message ... Should "filename" be the name of my workbook? And is A1 referring to the shList or to the cell that I'm entering this formula into? When I enter the formula as given, it returns #VALUE! "vezerid" wrote: To get, in a formula, the current sheet name, you can use the following expression: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) In a separate area in your workbook you can have a list of all worksheets, in the order in which they appear in Excel (and you want to use them). Say this is in sheet List and occupies cells A1:A10. You can define a name (InsertNameDefine...) for this range, say shList. Now, instead of pointing to the data of a previous sheet with a formula such as: ='Sheet 1'!A1 You can use INDIRECT in the following manner: =INDIRECT("'"&INDEX(shList,MATCH(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1)) +1,255),shList,0)-1)&"'!A1") You can then copy this formula in any other sheet and it will refer to the previous sheet. HTH Kostis Vezerides On Jul 5, 6:20 pm, Susan wrote: How can I reference a worksheet tab in a formula, so that it always refers to the previous tab in a workbook, such as a formula in Tab 3 needs info from the same cell in Tab 2 in order to keep track of balances. When I copy and paste the spreadsheets into a new worksheet it keeps the reference that was in the original worksheet. Susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
static reference in formula to worksheet | Excel Worksheet Functions | |||
two worksheet reference formula help | Excel Worksheet Functions | |||
Cell reference in different worksheet in formula | Excel Worksheet Functions | |||
A formula in this worksheet contains one or more invalid reference | Excel Discussion (Misc queries) | |||
Worksheet name / reference as a formula? | Excel Discussion (Misc queries) |