Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
#2
![]() |
|||
|
|||
![]()
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. |
#3
![]() |
|||
|
|||
![]()
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. |
#4
![]() |
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
I want to print out the sheet tabs (sheet names) | Excel Worksheet Functions | |||
Referencing an Entire Sheet | Excel Discussion (Misc queries) | |||
Sheet tabs disappear sometimes in Internet Explorer | Excel Discussion (Misc queries) | |||
Naming & renaming a sheet tab | Excel Worksheet Functions | |||
Graphs and sheet referencing? | Excel Worksheet Functions |