I agree, as an option would be rally useful, for instance copy and paste
special and have an options paste sheet relative or a built in function.
Here's a UDF by Harlan Grove
-----------------------------------------------------------------------------------------------
Function showoff( _
r As Range, _
s As Long, _
Optional rr As Boolean = True) As Variant
'-----------------------------------------------------------
Application.Volatile
s = s + r.Parent.Index
If s < 1 Or s Worksheets.Count Then
showoff = CVErr(xlErrRef)
ElseIf rr Then
Set showoff = Worksheets(s).Range(r.Address)
Else
showoff = Worksheets(s).Range(r.Address).Value
End If
End Function
------------------------------------------------------------------------------------------------
Use as
=showoff(A1,-1)
will return what's in A1 in the previous sheet, -2 returns what's in the
second but previous sheet and so on
how to install
http://www.mvps.org/dmcritchie/excel/install.htm
Regards,
Peo Sjoblom
"RonMc5" wrote:
Thanks for the amazingly quick reply. However I already knew it didn't work.
I haven't done any macros, so I was hoping for a more detatailed response to
get me started. I don't even know what a UDF is. I will try to find out
however.
Relative sheet responses WOULD be very useful though, don't you think?
"Peo Sjoblom" wrote:
Relative referencing does not work between sheets so you either have to use a
macro, UDF or do an edit replace
http://tinyurl.com/4ftky
Regards,
Peo Sjoblom
"RonMc5" wrote:
I use a workbook with 53 sheet to keep track of mileage info for expenses.
There are two references to the previous sheet. EG: Current sheet is sheet5!
I need to reference a cell sheet4! . I can do this now, but if I copy sheet 5
to sheet 6, the reference is still to sheet 4, instead of to sheet 5 so I
have to edit the refering cell each time to I copy to a new sheet. 51 times!
:) The last sheet is for summaries and totals and tax info.
I think I should be able to say sheet(-1)! . That looks like a good syntax
to me! :)
Any work arounds please?