Home/ Questions/Q 4621
Next
In Process
Computing Staff
  • 1
Asked: 2021-11-11T06:24:56+00:00 In:

Automaticaly Increment Date By 1 On Each Sheet Of A Workbook

  • 1

I have an Fiscal Year file. Each sheet tab is a month with identical formats. I want to increment the first month (January) by 1, keeping the year the same until I get to the end of the fiscal year.

My file begins at the 3rd quarter and ends at the 4th quarter. I typed in the first month, January 2017 on the first sheet tab.

How do i put in a formula to increase January 2017 to February 2017 on the second sheet tab in this file?

I will begin the FY-18 with July 2017 and include all four quarters through June 2018.

Thank you

Share

    1 Answer

    1. You missed a step. A UDF doesn’t work unless there is VBA code behind it. Think of it this way:

      When you use any built in Excel function, e.g. SUM, there is some internal programming behind it, buried deep within Excel. The nice folks that wrote Excel provide that programming for you.

      When you create and use a User Defined Function, you, the user, are responsible for providing that behind the scenes programming.

      You are getting a #NAME error because Excel doesn’t recognize the function PrevSheet as a valid function. You would get the same error if you entered any “function name” that Excel doesn’t know what to do with, e.g. =HappyBirthday(A1)

      However, if Excel can find some programming behind the function name, it will (hopefully) return the value that the user wants the function to return.

      That programming is done with Excel VBA – Visual Basic for Applications. VBA is a programming language that allows users to do things with Excel that go way beyond the built in functions. The set of instructions that are written in VBA are often called a Macro. The main difference between a Macro and a UDF is that a Macro is run either manually or automatically via a specific action taken by a user while a UDF can be run by including the name of the UDF in a formula.

      Read up on Excel VBA and/or Excel Macros for more info.

      OK, all that said, what you want is a Function that will always return the name of the previous sheet based on where the function is used. Since Excel doesn’t have a built in function to do that, we can use a UDF. Here’s how:

      1 – Follow the first 3 steps at the link below to open the VBA editor and insert a Module.

      https://www.wikihow.com/Create-a-Us…

      2 – Once that Module is open, Paste the VBA code shown below into the window. That VBA code is the programming behind the UDF. The code first determines what sheet it is used in and then returns the name of the previous sheet, returning the value in the cell that you use in the formula, e.g. A1.

      Function PrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
'Determine the sheet number of the sheet that called the UDF
    i = rCell.Cells(1).Parent.Index
'Subtract 1 from the current sheet number to reference the previous sheet
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function

      3 – Now go back into your sheet and try the formula I suggested, which has the PrevSheet UDF in it.

      Just like any Nested formula, Excel will see the UDF called PrevSheet and run the programming that is behind it.

      One more item:

      Since the workbook will now contain some VBA code, you will need to save the workbook with the .xlsm file type.

      How To Post Data or Code —> Click Here Before Posting Data or VBA Code

      • 0