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. |
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. |
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. |
All times are GMT +1. The time now is 10:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com