Carrying data over in worksheets
Thank you!
"Gord Dibben" wrote:
Maybe you could use the User Defined Function PrevSheet for the "carrying
over".
Function PrevSheet(rg As Range)
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 65 sheets, sheet1 through sheet65...........sheet names don't
matter.
In sheet1 you have a formula in A10 =SUM(A1:A9)
Select second sheet and SHIFT + Click last sheet
In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)
Ungroup the sheets.
Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9
To enter new products on each sheet you can also group the sheets and add a new
product on the activesheet, which will be replicated on each sheet.
Gord
On Tue, 4 Dec 2007 20:03:02 -0800, roxiemayfield
wrote:
This is an inventory workbook. Worksheet 1's ending inventory carries over to
Worksheet 2's beginning inventory, etc.
I want to be able to insert lines for new products and have that line appear
on all the worksheets with the formulas intact.
"Gord Dibben" wrote:
What exactly do you mean by "carry over"?
A running total from sheet1 to sheet2 to sheet3 etc.?
Or you just want to insert a row in each sheet at the same time?
On Tue, 4 Dec 2007 19:25:00 -0800, roxiemayfield
wrote:
I have a workbook that has 65 worksheets. Each worksheet carries over info
from the previous worksheet. How do I insert a line in one sheet and it
carry over to the following sheets. Is there any way to do this?
|