Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Sheet
My boss fills out her timecards in Quattropro. She wants to do a running
tally of her vacation time so this requires Quattropro to look at the previous sheet and add a static number. The formula she currently uses in Quattropro is the following: @sum(010706:A22 + 4.46). 010706 is the sheet prior to the current sheet she is working on, 011406. When she copies this formula to the next sheet it changes to reference the sheet she is copying from. So if her next sheet is entitled 012106 the formula changes to @sum (011406:A22 + 4.46). Is this possible in Excel as we are transitioning completely to Excel from Quattropro. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Sheet
Excel could undoubtedly do it, but differently. -- raypayette ------------------------------------------------------------------------ raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569 View this thread: http://www.excelforum.com/showthread...hreadid=565743 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Sheet
Excel is not bright enough to change the sheet reference when the formula is
copied to the next sheet. You can EditReplace after the fact to change the sheet name which is probably easiest. 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 12 sheets, sheet1 through sheet12. Select sheet2 and SHIFT + Click sheet31 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 Thu, 27 Jul 2006 19:05:49 GMT, "bg19299" <u24604@uwe wrote: My boss fills out her timecards in Quattropro. She wants to do a running tally of her vacation time so this requires Quattropro to look at the previous sheet and add a static number. The formula she currently uses in Quattropro is the following: @sum(010706:A22 + 4.46). 010706 is the sheet prior to the current sheet she is working on, 011406. When she copies this formula to the next sheet it changes to reference the sheet she is copying from. So if her next sheet is entitled 012106 the formula changes to @sum (011406:A22 + 4.46). Is this possible in Excel as we are transitioning completely to Excel from Quattropro. Thank you. Gord Dibben MS Excel MVP |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying Sheet
Thank you very much Gord. I will try and put this into practice.
Gord Dibben wrote: Excel is not bright enough to change the sheet reference when the formula is copied to the next sheet. You can EditReplace after the fact to change the sheet name which is probably easiest. 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 12 sheets, sheet1 through sheet12. Select sheet2 and SHIFT + Click sheet31 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 My boss fills out her timecards in Quattropro. She wants to do a running tally of her vacation time so this requires Quattropro to look at the [quoted text clipped - 5 lines] (011406:A22 + 4.46). Is this possible in Excel as we are transitioning completely to Excel from Quattropro. Thank you. Gord Dibben MS Excel MVP -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200607/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum up columns in different sheet with error check | Excel Discussion (Misc queries) | |||
copying sheet references that refer to a cell in the preceding she | Excel Worksheet Functions | |||
copying charts into new sheets, data is pulled from old sheet | Charts and Charting in Excel | |||
Copying a total from another Sheet | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |