Relative Addressing

[Guest]

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

 

[Harald Staff]

Hi Bill

Unfortunately, not that I know of. Relative sheet addressing is on many
people's with list.

HTH. Best wishes Harald

 

[Gord Dibben]

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

 

[Guest]

Bill

As Harald points out, no built-in function.

Here's a User Defined Function.

Thanks Gord. I'll tinker with it some.

Bill

 

[Guest]

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