Thread: How to write a formula which accesses the just previous sheet

I think this is what you are after - I have attached a sample workbook with values in B4 and I am placing the formula of (current sheet b4 - previous sheet B4) into cell C5 - I have made the sheets not just Sheet 1, Sheet 2 etc - so this should work no matter what the worksheet is called. You could run this macro when you need to add the formulas to the sheets. To get to the code open the workbook and hit ALT F11 and this will take you to the VB Editor. The code is inside Module 1 - double click on Module 1 to see the code. Close the VB Editor once you have had a look at the code in the module. When you are back to the workbook to run the macro you need to select macros from the Developer tab in the workbook or hit ALT F8 and this will bring up a list of available macros to run - select macro called addFormula to run the macro.
Let me know if it does what you are after or need further help.
Here is a link to more information about macros http://office.microsoft.com/en-us/ex...010014113.aspx

Sub addFormula()
Dim ws As Worksheet, wsPr As String, x As Long
For Each ws In ActiveWorkbook.Worksheets
If x > 0 Then
ws.Range("C5").Formula = "=B4-" & wsPr & "!B4"
End If
x = x + 1
wsPr = ws.Name
Next
End Sub

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”