Solved Automaticaly increment date by 1 on each sheet of a workbook

January 14, 2018 at 14:01:33
Specs: Windows 7
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



See More: Automaticaly increment date by 1 on each sheet of a workbook

Report •

✔ Best Answer
January 15, 2018 at 18:09:51
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



#1
January 14, 2018 at 19:20:04
re: "How do i put in a formula to increase January 2017 to February 2017 on the second sheet tab in this file?"

When you use the word "tab" do you mean the Sheet tab that shows the name of each Sheet, e.g. Sheet1, Sheet2, Sheet3?

If so, then you'll need to use VBA. There is no cell-based formula that can modify the Sheet tab.

Is that what you are trying to do? If not please clarify your request.

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


Report •

#2
January 15, 2018 at 04:13:09
I am sorry I used incorrect terminology. I meant SHEET.

January 2017 is in cell A! on Sheet 1. I want February 2017 in cell A1 on Sheet 2,

Note: When I had all of the months on the same sheet, I used the formula:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) to increase each month by one. This worked perfectly.

I am drawing a blank on structuring cell based formula to reference the PREVIOUS sheet date for an increment of one month, I am getting errors.

THANK YOU


Report •

#3
January 15, 2018 at 07:26:28
First, copy the UDF (User Defined Function) shown here into a module in the VBA editor:

https://excel.tips.net/T003088_Rela...

Then modify your formula as follows, starting in the second sheet:

=DATE(YEAR(PrevSheet(A1)),MONTH(PrevSheet(A1))+1,DAY(PrevSheet(A1)))

Another option is to use the DATEVALUE function to grab the current Month name from somewhere else in your sheet. e.g. if Cell C4 in every worksheet contained the current month name, this might work:

=DATEVALUE("1"&C4&"2018")

The following formula method uses INDIRECT to increment formulas each new sheet by 1 day, based on the previous sheet, but it involves the use of multiple formulas. Perhaps you could modify it for your monthly use.

https://answers.microsoft.com/en-us...

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


Report •

Related Solutions

#4
January 15, 2018 at 08:05:35
Thank you for your response. I am not an advanced Excel user so I am not sure I understood your response..

I copied your UDF:
=DATE(YEAR(PrevSheet(A1)),MONTH(PrevSheet(A1))+1,DAY(PrevSheet(A1)))

I modified it to:
=DATE(YEAR(Sheet1(A1)),MONTH(Sheet1(A1))+1,DAY(Sheet1(A1)))

The results in cell A1 is #NAME?

Thank you


Report •

#5
January 15, 2018 at 18:09:51
✔ Best Answer
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


Report •

#6
January 16, 2018 at 15:47:39
THANK YOU ! ! ! ! your directions provided the results I was seeking ! ! !

Last Questions: Do I have to change the filename now that the macro is part of the file? I am getting a message when I attempt a general save.

When I start another fiscal year, with the same sheets and format can I just duplicate the current file and save with a different name?


Report •

#7
January 16, 2018 at 16:17:20
re: "I am getting a message when I attempt a general save."

Allow me to offer you a posting tip for when you ask for help in a forum such as this...

We can't see your workbook from where we're sitting. Telling us that you are "getting a message" doesn't give us much to work with. Why not tell us what the message says?

message edited by DerbyDad03


Report •

#8
January 16, 2018 at 21:55:03
The message related to saving the file as a Macro enabled file--which I did. I was able to save the FY_17 macro-enabled file with a different filename-- FY_18.

In the new file, when I changed the first month to 01/01/2018, all other months in this new file were changed to the correct dates.

Thanks again for all of your help. This has been a great learning experience for me--both in learning about macros and using this forum.

I appreciate your patience and assistance in helping me,


Report •

#9
Report •

Ask Question