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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 02:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com