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