Copying Sheet

[bg19299]

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.

 

[raypayette]

Excel could undoubtedly do it, but differently.

 

[Gord Dibben]

Excel is not bright enough to change the sheet reference when the formula is
copied to the next sheet.

You can Edit>Replace 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 Insert>Module. 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
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

 

[bg19299 via OfficeKB.com]

Thank you very much Gord. I will try and put this into practice.

Excel is not bright enough to change the sheet reference when the formula is
copied to the next sheet.

You can Edit>Replace 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 Insert>Module. 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