Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I enable the "Record Using Relative References" option in. | Excel Discussion (Misc queries) | |||
Copy formula...sheet 2 sheet | New Users to Excel | |||
Need Macro to copy specific sheet | Excel Worksheet Functions | |||
Making the Sheet a relative value? | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |