Referencing Sheet Tabs
 

Excel 2003. I produce a weekly cash report. Each new weekly report refers to
several cells on the previous week's sheet. I produce each new sheet by
copying the last one and changing its name. I then have to update the
formulas so that they look at the now previous sheet. Is there some way to
enter the sheet tab name into a formula so that the name of the tab is taken
from another cell.

Hi captain,

Lets say
cell A1 contains the sheet name
cell A2 contains the row no
cell A3 contains the column no
of the cell you wish to find.

Then =ADDRESS(A2,A3,,,A1) will give you the cell address
and =INDIRECT(ADDRESS(A2,A3,,,A1)) will give
the contents of that cell.

Ed Ferrero

"captain stephen" <captain wrote in
message ...
Excel 2003. I produce a weekly cash report. Each new weekly report refers
to
several cells on the previous week's sheet. I produce each new sheet by
copying the last one and changing its name. I then have to update the
formulas so that they look at the now previous sheet. Is there some way to
enter the sheet tab name into a formula so that the name of the tab is
taken
from another cell.

Sorted. Thanks for that.

"Ed Ferrero" wrote:

Hi captain,

Lets say
cell A1 contains the sheet name
cell A2 contains the row no
cell A3 contains the column no
of the cell you wish to find.

Then =ADDRESS(A2,A3,,,A1) will give you the cell address
and =INDIRECT(ADDRESS(A2,A3,,,A1)) will give
the contents of that cell.

Ed Ferrero

"captain stephen" <captain wrote in
message ...
Excel 2003. I produce a weekly cash report. Each new weekly report refers
to
several cells on the previous week's sheet. I produce each new sheet by
copying the last one and changing its name. I then have to update the
formulas so that they look at the now previous sheet. Is there some way to
enter the sheet tab name into a formula so that the name of the tab is
taken
from another cell.

Captain

If you're willing to go with a user defined function.

Copy/paste this User Defined Function to a general module in your workbook.

Function PrevSheet(rg As Range)
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

'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.

When you copy this sheet, the formulas will refer to the prior sheet as long
as the prior sheet is to the left of new sheet.


Gord Dibben Excel MVP

On Thu, 31 Mar 2005 15:05:06 -0800, captain stephen <captain
wrote:

Excel 2003. I produce a weekly cash report. Each new weekly report refers to
several cells on the previous week's sheet. I produce each new sheet by
copying the last one and changing its name. I then have to update the
formulas so that they look at the now previous sheet. Is there some way to
enter the sheet tab name into a formula so that the name of the tab is taken
from another cell.