Creating a sequential date over multiple tabs.

**Snookem** · 05-27-2013, 12:20 AM

Hi,

I have a spreadsheet with around 31 tabs (for the months of Jan, Feb, March etc).

I know how to do =Jan 1!A1+1 on Jan 2 and =Jan 2!A1+1 on Jan 3 etc or =Jan 1!A1+1 on Jan 2 and =Jan 1!A1+2 on Jan 3 etc and that works but I would have to do that on every sheet so I may as well just manually write the date in on each. I would like a formula so that I can put 1/1/13 in A1 on the first tab and it will automatically put 2/1/13 in A1 on the second tab, then 3/1/13 in A1 on the third tab and so on.

I got the below off a forum here but for the life of me, it is not working.

Can someone please help?

Re: Sequential Dates Over Multiple Worksheets

It could be as simple as

=Sheet01!B1+1 on sheet02 and =Sheet02!B1+1 on sheet03 etc.

Alternatively, to avoid having to create a separate version of the formula on each sheet, you could use the following user defined function.

Please Login or Register  to view this content.

To use this, open the VBA editor (Alt F11) , add a new module (right click on the appropriate VBA project - insert- module) and paste the above in.

Then add the formula

=AddSequentialDate("B1",B1)

to each B2 on sheets 02 onwards. The function works out which sheet it is on and adds one to the sheet before.

Hope this helps.

**AndyLitch** · 05-27-2013, 02:21 AM

The function needs to be in a code module and the syntax takes the form :-

Please Login or Register  to view this content.

sheet1 A1 would have day 1............. A1 in all other sheets would have the above function and display sequential dates.
Hope this helps

**Snookem** · 05-27-2013, 02:38 AM

Originally Posted by AndyLitch

The function needs to be in a code module and the syntax takes the form :-

Please Login or Register  to view this content.

sheet1 A1 would have day 1............. A1 in all other sheets would have the above function and display sequential dates.
Hope this helps

Nope... Still no luck, the box just displays #NAME?

**humdingaling** · 05-27-2013, 04:09 AM

Please Login or Register  to view this content.

this macro will insert all the dates for you, though it would not be formula

**Snookem** · 05-29-2013, 11:34 PM

Originally Posted by humdingaling

Please Login or Register  to view this content.

this macro will insert all the dates for you, though it would not be formula

I will try this but where do i put it? In the VBA?

**humdingaling** · 05-29-2013, 11:59 PM

yes
put into module in VBA

**Snookem** · 05-30-2013, 12:24 AM

Originally Posted by humdingaling

yes
put into module in VBA

Okay I think I need a little more direction on this... I'm still new with the VBA.

I put that first code you gave me in a new module just for the first tab one or all tabs?

Then do I put anything in A1 on each tab?

Sorry to be a pain and thanks for your help

**humdingaling** · 05-30-2013, 12:33 AM

the way the macro works
you enter first date into first tab "A1" ie 30th May
run macro
it will run thru every single tab and put the next day into A1 into subsequent tabs
ie 2nd tab = 31st may, 3rd = 1st june, etc

i have attached the macro into example spreadsheet

you can either assign it into your own workbook (like the example) or you can assign it into your own personal macro list

**Snookem** · 05-30-2013, 01:54 AM

Originally Posted by humdingaling

the way the macro works
you enter first date into first tab "A1" ie 30th May
run macro
it will run thru every single tab and put the next day into A1 into subsequent tabs
ie 2nd tab = 31st may, 3rd = 1st june, etc

i have attached the macro into example spreadsheet

you can either assign it into your own workbook (like the example) or you can assign it into your own personal macro list

VOILA!Amazing it worked

Thank you very much :D

**humdingaling** · 05-30-2013, 02:05 AM

glad it worked

If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

**andrewp0502** · 09-08-2020, 01:56 AM

This module worked for me as well, however, the issue I am running into is when I save the excel file and then goto re-open it and change the date in the first sheet (i.e. now I want to auto-fill all December's dates) I change it to 12/1/20 yet sheets 2 thru 30 do not auto adjust to the december dates. Instead they stay as is, on the date they previously showed. I have to re-group all these sheets, and re-enter the =Addsequentialdate formula for them to adjust. Is there a way to fix this so I can open this file anytime, change the date in the first sheet and every sheet after will adjust correctly? Thank you!

**FDibbins** · 09-09-2020, 12:04 AM

Originally Posted by andrewp0502

This module worked for me as well, however, the issue I am running into is when I save the excel file and then goto re-open it and change the date in the first sheet (i.e. now I want to auto-fill all December's dates) I change it to 12/1/20 yet sheets 2 thru 30 do not auto adjust to the december dates. Instead they stay as is, on the date they previously showed. I have to re-group all these sheets, and re-enter the =Addsequentialdate formula for them to adjust. Is there a way to fix this so I can open this file anytime, change the date in the first sheet and every sheet after will adjust correctly? Thank you!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Creating a sequential date over multiple tabs.

LinkBack

Thread Tools

Rate This Thread

Display

Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Re: Creating a sequential date over multiple tabs.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions