Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
RonMc5
 
Posts: n/a
Default relative sheet references ala sheet(-1)!B11 so I can copy a sheet.

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?
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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?

  #3   Report Post  
RonMc5
 
Posts: n/a
Default

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?

  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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?

  #5   Report Post  
Gord Dibben
 
Posts: n/a
Default

Ron

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 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

Say you have 53 sheets, sheet1 through sheet53.

Select sheet2 and SHIFT + Click sheet5

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Tue, 1 Feb 2005 11:13:02 -0800, 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?




  #6   Report Post  
RonMc5
 
Posts: n/a
Default

Gord,
Thank you very much for the detailed reply. I don't understand it yet, but I
am sure now that I have this I will learn a lot in the process of using it.

I was thinking UDF was an UnDocumented Feature! :)

Ron

"Gord Dibben" wrote:

Ron

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 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

Say you have 53 sheets, sheet1 through sheet53.

Select sheet2 and SHIFT + Click sheet5

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Tue, 1 Feb 2005 11:13:02 -0800, 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?



  #7   Report Post  
RonMc5
 
Posts: n/a
Default

Gord,
Damn you're good! You deserve the MVP!! It worked once I got rid of some
spelling bugs. :) I am adding this to my repetoir and will go to the site
you showed me and learn more. It's a shame at these prices some tutorials
don't come with the package. When I searched help for UDF or User Defined
Functions, I got ZIP.
I guess they want you to pay more cubic dollars to find out how to use this
stuff. BTW, I have been using spreadsheets as an amatuer since Visicalc
spread (pun intended) to the CP/M world and we were happy with our Z80s! :)

"Gord Dibben" wrote:

Ron

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 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

Say you have 53 sheets, sheet1 through sheet53.

Select sheet2 and SHIFT + Click sheet5

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Tue, 1 Feb 2005 11:13:02 -0800, 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?



  #8   Report Post  
RonMc5
 
Posts: n/a
Default

Thanks to you too Peo, but I had already implemented the one from Gord when I
read your 2nd reply. I will analyse yours as a learning tool as well though,
and the site you pointed out as well. Still learning at 61! :)

Ron


"Peo Sjoblom" wrote:

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?

  #9   Report Post  
Gord Dibben
 
Posts: n/a
Default

Ron

The UDF PrevSheet code is not mine(sorry, non attributed) but the instructions
on how to use it are.

So......only half "damn good"<g


Gord

On Wed, 2 Feb 2005 09:29:05 -0800, RonMc5
wrote:

Gord,
Damn you're good! You deserve the MVP!! It worked once I got rid of some
spelling bugs. :) I am adding this to my repetoir and will go to the site
you showed me and learn more. It's a shame at these prices some tutorials
don't come with the package. When I searched help for UDF or User Defined
Functions, I got ZIP.
I guess they want you to pay more cubic dollars to find out how to use this
stuff. BTW, I have been using spreadsheets as an amatuer since Visicalc
spread (pun intended) to the CP/M world and we were happy with our Z80s! :)

"Gord Dibben" wrote:

Ron

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 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

Say you have 53 sheets, sheet1 through sheet53.

Select sheet2 and SHIFT + Click sheet5

In B1 enter =PrevSheet(A1)

Ungroup the sheets.

Each B1 will have the contents of the previous sheet's A1

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

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

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Tue, 1 Feb 2005 11:13:02 -0800, 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?




  #10   Report Post  
Harlan Grove
 
Posts: n/a
Default

Peo Sjoblom wrote...
Relative referencing does not work between sheets so you either have

to use a
macro, UDF or do an edit replace

....

Not true! It can be done with built-in functions only *PLUS* an ordered
list of worksheet names. If the worksheets were named A, B, C, . . .,
BA (53 of 'em), then enter these worksheets names, one each per cell,
in a 53-row by 1-column range and name that range WSLST. Also create
the following defined names.

_FN_ referring to =CELL("Filename",INDIRECT("A1"))
WSN referring to =MID(_FN_,FIND("]",_FN_)+1,32)

Then enter the following formulas.

A!A1:
=1000000+1000*ROW()+COLUMN()

B!A1:
=2000000+1000*ROW()+COLUMN()

C!A1:
=3000000+1000*ROW()+COLUMN()

D!A1:
=4000000+1000*ROW()+COLUMN()

On each of these worksheets (A through D) fill A1 right into B1:D1,
then fill A1:D1 down into A2:D4. Then enter the following formulas for
comparison.

B!A6:
=A1-A!A1

B!A11:
=A1-INDIRECT("'"&INDEX(WSLST,MATCH(WSN,WSLST,0)-1)&"'!"&CELL("Address",A1))

Fill B!A6 right into B:B6:D6 then B:A6:D6 down into B!A7:D9. Fill B!A11
right into B!B11:D11 then B!A11:D11 down into B!A12:D14. Now copy
B!A6:D14 and paste into C!A6 and D!A6. Note that on C, the A6:D9 range
becomes 2000000 while the A11:D14 range remains 1000000; on D, the
A6:D9 range becomes 3000000 while the A11:D14 range remains 1000000.

This is fragile in the sense that the INDIRECT formulas critically
depend on WSLST containing the worksheet names in order. Inserting,
deleting or reordering worksheets will thoroughly screw up these
formulas unless you update WSLST.

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
How can I enable the "Record Using Relative References" option in. cbaugher Excel Discussion (Misc queries) 1 January 31st 05 04:32 PM
Copy formula...sheet 2 sheet Mick New Users to Excel 0 January 26th 05 02:58 AM
Need Macro to copy specific sheet mac Excel Worksheet Functions 1 January 17th 05 09:46 PM
Making the Sheet a relative value? AuMiQuinn Excel Worksheet Functions 4 January 5th 05 08:38 PM
Naming & renaming a sheet tab Cgbilliar Excel Worksheet Functions 1 November 7th 04 06:57 PM


All times are GMT +1. The time now is 02:44 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"