Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Relative reference to a cell on a previous sheet

Hi,

I need a reference to a previous sheet where the cell reference is not
absolute - ie automatically updates/changes when I move the information
on the reference sheet. Ie:If I have a reference on Sheet "Week 30
2006" to ='Week 29 2006'!C6+1 and I add a row, it becomes ='Week 29
2006'!C7+1.

The problem is that if I use any of the UDF I have found on the web,
the cell reference doesn't change as I move/add information: eg

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = 0
Exit Function
End If
PrevSheet = Sheets(Application.Caller.Parent.Index -
1).Range(rg.Address).Value
End Function

'and in the worksheet, enter
'=PrevSheet(E3)
Then PrevSheet(E3) does not become PrevSheet(E4).

I've also tried with indirect:

Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

where the worksheet formula becomes =INDIRECT("'"&PrevSheet()&"'!C6")

Again - C6 is absolute.

Can anyone help me with this?

Thank you so much
Helen

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Relative reference to a cell on a previous sheet

In the first UDF (called with =PrevSheet(E3)), you're actually passing a
reference to the sheet with the formula. If you insert a new row on that sheet
(between 1 and 3), you'll see that the reference changes.

In the second version (=INDIRECT("'"&PrevSheet()&"'!C6")), you're passing a
string.

In both cases, excel doesn't know anything about the sheet that it should be
"monitoring" for changes.

I'll be surprised if anyone can come up with a nice solution for the both the
previous sheet stuff and inserting/deleting rows/columns on that previous sheet.

I've been surprised before, but this doesn't appear possible to me.

Is there a chance that you have a column of unique keys in that worksheet where
you could use =prevsheet() and maybe index/match? The formula wouldn't adjust,
but you may get the result you want.


wrote:

Hi,

I need a reference to a previous sheet where the cell reference is not
absolute - ie automatically updates/changes when I move the information
on the reference sheet. Ie:If I have a reference on Sheet "Week 30
2006" to ='Week 29 2006'!C6+1 and I add a row, it becomes ='Week 29
2006'!C7+1.

The problem is that if I use any of the UDF I have found on the web,
the cell reference doesn't change as I move/add information: eg

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = 0
Exit Function
End If
PrevSheet = Sheets(Application.Caller.Parent.Index -
1).Range(rg.Address).Value
End Function

'and in the worksheet, enter
'=PrevSheet(E3)
Then PrevSheet(E3) does not become PrevSheet(E4).

I've also tried with indirect:

Function PrevSheet()
On Error GoTo EndFunction
Application.Volatile True
PrevSheet = Application.Caller.Worksheet.Previous.Name
Exit Function
EndFunction:
With Application.Caller.Parent.Parent.Worksheets
PrevSheet = .Item(.Count).Name
End With
End Function

where the worksheet formula becomes =INDIRECT("'"&PrevSheet()&"'!C6")

Again - C6 is absolute.

Can anyone help me with this?

Thank you so much
Helen


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy a relative reference formula from one sheet to another. jannkatt Excel Discussion (Misc queries) 3 May 17th 06 07:13 PM
Referencing cell in another sheet yields null? [email protected] Excel Worksheet Functions 4 November 18th 05 02:11 AM
Input cell reference is not valid (One Variable Data Table) Dottore Excel Worksheet Functions 9 September 1st 05 03:05 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. RonMc5 Excel Discussion (Misc queries) 9 February 3rd 05 01:51 AM


All times are GMT +1. The time now is 02:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"