referring to previous sheet
 

I have a list of several sheets, named with numbers (1,2,3,ecc.). I need
some kind of function that allows me to have the value of one cell equal
to that same cell in 1 to 3 sheets before. Example:

sheet5

A3= value of A3 in sheet2 (5-3)

How is that possible?


--
Iolao
------------------------------------------------------------------------
Iolao's Profile: http://www.excelforum.com/member.php...o&userid=16654
View this thread: http://www.excelforum.com/showthread...hreadid=320575

the simplest is

=sheet2!a3

--
Don Guillett
SalesAid Software

"Iolao" wrote in message
...

I have a list of several sheets, named with numbers (1,2,3,ecc.). I need
some kind of function that allows me to have the value of one cell equal
to that same cell in 1 to 3 sheets before. Example:

sheet5

A3= value of A3 in sheet2 (5-3)

How is that possible?


--
Iolao
------------------------------------------------------------------------
Iolao's Profile:
http://www.excelforum.com/member.php...o&userid=16654
View this thread: http://www.excelforum.com/showthread...hreadid=320575

If your sheets are named "1", "2", rather than your example ("sheet5",
"sheet2"), one way:

=INDIRECT(MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+
1, 255)-3 & "!A3")

Note that the file has to be saved for that to work.



In article ,
Iolao wrote:

I have a list of several sheets, named with numbers (1,2,3,ecc.). I need
some kind of function that allows me to have the value of one cell equal
to that same cell in 1 to 3 sheets before. Example:

sheet5

A3= value of A3 in sheet2 (5-3)

How is that possible?

Iolao

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.

If not familiar with VBA and macros see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP

On Fri, 26 Nov 2004 08:24:33 -0600, Iolao
wrote:


I have a list of several sheets, named with numbers (1,2,3,ecc.). I need
some kind of function that allows me to have the value of one cell equal
to that same cell in 1 to 3 sheets before. Example:

sheet5

A3= value of A3 in sheet2 (5-3)

How is that possible?