Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Relative Addressing
One can write a formula such as [ ]=A1 and copy it down a column and
relative addressing does what it does and makes life easy. My question is whether a similar capability exists between sheets? I'd like to say for example [ ]=PrevSheet!A1 and then be able to copy that across sheets as well as down columns. I've only found how to do this by either explicitly manually plugging in the name of the previous sheet into the formula, or by manually putting the name of the previous sheet into a cell on the current sheet and then using indirect addressing. Excel obviously knows the names of all my sheets and the order that I have them displayed on screen so it has all the info it needs to do what I want. Is there some simple approach which has escaped me? Thanks.... Bill |
#2
|
|||
|
|||
Hi Bill
Unfortunately, not that I know of. Relative sheet addressing is on many people's with list. HTH. Best wishes Harald "Bill Martin -- (Remove NOSPAM from address)" skrev i melding ... One can write a formula such as [ ]=A1 and copy it down a column and relative addressing does what it does and makes life easy. My question is whether a similar capability exists between sheets? I'd like to say for example [ ]=PrevSheet!A1 and then be able to copy that across sheets as well as down columns. I've only found how to do this by either explicitly manually plugging in the name of the previous sheet into the formula, or by manually putting the name of the previous sheet into a cell on the current sheet and then using indirect addressing. Excel obviously knows the names of all my sheets and the order that I have them displayed on screen so it has all the info it needs to do what I want. Is there some simple approach which has escaped me? Thanks.... Bill |
#3
|
|||
|
|||
Bill
As Harald points out, no built-in function. Here's a User Defined Function. Function PrevSheet(rg As Range) 'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1. 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 Group sheet2 through 22 and in A1 enter the formula =PrevSheet(B2) to replicate across sheets. DO NOT FORGET to ungroup sheets when done. Gord Dibben Excel MVP On Sat, 19 Feb 2005 14:40:15 -0500, "Bill Martin -- (Remove NOSPAM from address)" wrote: One can write a formula such as [ ]=A1 and copy it down a column and relative addressing does what it does and makes life easy. My question is whether a similar capability exists between sheets? I'd like to say for example [ ]=PrevSheet!A1 and then be able to copy that across sheets as well as down columns. I've only found how to do this by either explicitly manually plugging in the name of the previous sheet into the formula, or by manually putting the name of the previous sheet into a cell on the current sheet and then using indirect addressing. Excel obviously knows the names of all my sheets and the order that I have them displayed on screen so it has all the info it needs to do what I want. Is there some simple approach which has escaped me? Thanks.... Bill |
#4
|
|||
|
|||
Gord Dibben wrote:
Bill As Harald points out, no built-in function. Here's a User Defined Function. Thanks Gord. I'll tinker with it some. Bill |
#5
|
|||
|
|||
Harald Staff wrote:
Hi Bill Unfortunately, not that I know of. Relative sheet addressing is on many people's with list. ----------------- Ah well. At least I get some small satisfaction that it wasn't right there staring me in the face. Thanks. Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indexing / Cell Addressing | Excel Discussion (Misc queries) | |||
Offset() relative addressing Row parameter | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Relative Macro Help on Keystrokes | Excel Discussion (Misc queries) | |||
Relative addressing using names or labels in formulas? | Excel Worksheet Functions |