How:Add New Worksheet that copies the cell of the previous sheet

[Guest]

I need to add worksheets all the time to a file we use. When I add the
worksheet I want it to automatically take the value of the previous worksheet
and add it to a cell on the new worksheet.

For example:
Step 1) Value in cell B1 on worksheet 1 is 2000. Formula in C1 is =+B1.
There is just one worksheet in the file.

Step 2) I insert new worksheet with exact formatting of worksheet 1. I want
worksheet2 Cell C1 to add cell C1 on worksheet1 to cell B1 on worksheet2.
Basically just rolling the formula to include the previous worksheet. I can
use a simple formula =B1+Sheet1!C1 Result: 3000 (assuming B1 of Worksheet2
is 1000)

Step 3) BUT- Then I want to insert another worksheet(3), again with the
exact same formatting but now I want cellC1 to pick up the the result from
Worksheet 2 Cell C1 and add it to B1 on worksheet3. This without me having to
actually type the formula to read =A2+Sheet!2B3.

I just want the formula to roll forward and include the previous worksheet's
cell. Reason for need: I need the file to be as simple as possible as it will
be used by very junior computer users in Mongolia! I need a new worksheet
every day.

This seems like a no-brainer but I can't figure it out! Does all that make
sense?

 

[Gord Dibben]

The worksheet that is added each day comes from where?

A default sheet template?

A copy of the existing sheet?

If from a sheet template, you could add this UDF to the workbook and place a
formula in the sheet template.

=B1 + PrevSheet(C1)

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
Application.Volatile
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


Gord Dibben MS Excel MVP