Making the Sheet a relative value?
 

As if that wasn't confusing enough...

Sheet 2 has a value in A15 which it's reading from Sheet 1's A15. How do I
copy Sheet 2 to a new sheet 3 so that Sheet 3's A15 points to Sheet 2's A15
and not Sheet 1's? How do I make the "=+Sheet1!A15" a relative value?

Hi
maybe the following helps you:
http://www.dicks-blog.com/archives/2...eet/trackback/

--
Regards
Frank Kabel
Frankfurt, Germany
"AuMiQuinn" schrieb im Newsbeitrag
...
As if that wasn't confusing enough...

Sheet 2 has a value in A15 which it's reading from Sheet 1's A15. How do
I
copy Sheet 2 to a new sheet 3 so that Sheet 3's A15 points to Sheet 2's
A15
and not Sheet 1's? How do I make the "=+Sheet1!A15" a relative value?

Sheets are absolute, I would personally just do a search and replace and
replace the sheet names. Here's a link to a UDF by John Walkenbach

http://www.j-walk.com/ss/excel/tips/tip63.htm


Regards,

Peo Sjoblom

"AuMiQuinn" wrote:

As if that wasn't confusing enough...

Sheet 2 has a value in A15 which it's reading from Sheet 1's A15. How do I
copy Sheet 2 to a new sheet 3 so that Sheet 3's A15 points to Sheet 2's A15
and not Sheet 1's? How do I make the "=+Sheet1!A15" a relative value?

Peo Sjoblom wrote...
Sheets are absolute, I would personally just do a search and replace
and
replace the sheet names. Here's a link to a UDF by John Walkenbach

http://www.j-walk.com/ss/excel/tips/tip63.htm

Walkenbach's SHEETOFFSET is a crippled udf. Unlike Excel's own OFFSET
function, SHEETOFFSET doesn't return range objects.

As an alternative,

http://groups-beta.google.com/group/...0f6e91b058ac38

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 Tue, 4 Jan 2005 14:37:04 -0800, AuMiQuinn
wrote:

As if that wasn't confusing enough...

Sheet 2 has a value in A15 which it's reading from Sheet 1's A15. How do I
copy Sheet 2 to a new sheet 3 so that Sheet 3's A15 points to Sheet 2's A15
and not Sheet 1's? How do I make the "=+Sheet1!A15" a relative value?