Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I edit several formulas at one time in a worksheet?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I edit several formulas at one time in a worksheet?
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 --------------- Jacob Skaria "oneil15" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I edit several formulas at one time in a worksheet?
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 On Tue, 7 Jul 2009 23:06:01 -0700, oneil15 wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
edit formulas on a worksheet simultaneously? | Excel Worksheet Functions | |||
How can i Edit many Cells at a time. | Excel Discussion (Misc queries) | |||
Some more help to edit some formulas | Excel Worksheet Functions | |||
Edit Paste Special Formulas | Excel Worksheet Functions | |||
hide formulas but can edit on the same cells | Charts and Charting in Excel |