Reference a Worksheet Tab in Formula

[Guest]

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

 

[vezerid]

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 (Insert>Name>Define...) 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("filename",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

 

[Gord Dibben]

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

 

[Guest]

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]

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 Insert>Module

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

 

[Harlan Grove]

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?]

 

[Guest]

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!

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 (Insert>Name>Define...) 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("filename",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

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

 

[Guest]

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.

 

[Peo Sjoblom]

Leave filename as it is, A1 is just there as a cell because the function
needs a cell reference


--
Regards,

Peo Sjoblom

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!

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 (Insert>Name>Define...) 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("filename",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

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

 

[Guest]

It is now returning NAME.

Leave filename as it is, A1 is just there as a cell because the function
needs a cell reference


--
Regards,

Peo Sjoblom

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!

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 (Insert>Name>Define...) 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("filename",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

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

 

[David Biddulph]

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

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!

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 (Insert>Name>Define...) 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("filename",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

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

 

[Peo Sjoblom]

Post the exact formula that returns the #NAME! error, you must have a typo
somewhere



--
Regards,

Peo Sjoblom

It is now returning NAME.

Leave filename as it is, A1 is just there as a cell because the function
needs a cell reference


--
Regards,

Peo Sjoblom

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!

:

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 (Insert>Name>Define...) 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("filename",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

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