How do I edit several formulas at one time in a worksheet?

[oneil15]

I have a workbook that contains a daily report. I add a new sheet for every
day of the month and right now I have to change all my formulas to reference
the day immediately before individually. Does anyone know of a way to edit
several formulas at one time to reference the sheet before them after copying
the entire sheet for a new daily report? The formula I am using is a 3D
reference formula that brings information from the previous day to the
current day.

 

[Jacob Skaria]

Hi

Instead of copying and orrect the formulas daily; you can have the formulas
using INDIRECT() which will pick the sheet names automatically; so that you
dont need to change the formulas...

---In the daily sheet you need to have a cell where you enter the current
date. Say cell A1
---Not sure how you have named your sheets. Say if you have named the sheet
in dd-mm-yyyy format
---So in case you have sheetnames with '06-07-2009', '07-07-2009' etc; With
current date (07-07-2009) in cell A1 of Sheet '07-07-2009' ; the below
formula in Sheet '07-07-2009' will refer to the previous days sheet cell A2

=INDIRECT("'" & TEXT(A1-1,"dd-mm-yyyy") & "'!A2")

If this post helps click Yes

 

[Gord Dibben]

Updating formulas to refer to previous month.

Copy/paste this UDF to a general module in your workbook.

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

Example of usage...................

Say you have 12 sheets, sheet1 through sheet12...........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


Gord Dibben MS Excel MVP