Dates incremented across sheets

bwoolley · Apr 16th, 2002, 09:15 AM

I have a workbook with 84 sheets, one for each day. I cannot figure out how to have excel automatically increment the date in the same cell on each sheet. For example, on day one(sheet 1) I have entered the start date of 3/18/02 into C5. I am trying to figure out how to get day 2 C5 to read 3/19/02.... day 84 to read 6/10/02 with out having to enter them all in manually. Any suggestions?

nisht · Apr 16th, 2002, 09:22 AM

Try with this code..

i have not checked this code.. as i have no 84 sheets...lol

Sub dayincrement()
Dim sht As Worksheet
Dim i As Integer
For Each sht In Worksheets
[c2] = Format([c2], "dd/mm/yyyy")
[c2] = [c2] + i
i = 1 + i
Next sht

End Sub

nishith desai
http://www.pexcel.com

bwoolley · Apr 16th, 2002, 09:24 AM

ok, I'm a novice.... where do I enter that code?

bwoolley · Apr 16th, 2002, 11:50 AM

**bump**

can anyone help me here?

Chris Davison · Apr 16th, 2002, 03:13 PM

are all your sheets called :

sheet 1
sheet 2
sheet 3
sheet 4

etc etc ?

if they are, we can use this 1,2,3,4,etc to our advantage....

or was that just an example ?

edit... I'm off to bed soon... if anyone sees this post maybe they can use the match replace thing in the filename to generate a number with which to increment the date by if the above question is "yes"

[ This Message was edited by: Chris Davison on 2002-04-16 14:18 ]

bwoolley · Apr 16th, 2002, 06:49 PM

Actually they are labled day 1, day 2....

nisht · Apr 17th, 2002, 08:43 AM

Press Alt + F11 and then slect vbaproject in left window... right click it.. and here select insert module.. now double click the module.. and you will see the code window.. paste the code there..

you can go to my site http://www.pexcel.com/download.htm and see how modules are placed.

nishith desai

nisht · Apr 17th, 2002, 08:43 AM

Press Alt + F11 and then slect vbaproject in left window... right click it.. and here select insert module.. now double click the module.. and you will see the code window.. paste the code there..

you can go to my site http://www.pexcel.com/download.htm and see how modules are placed.

nishith desai

bwoolley · Apr 17th, 2002, 01:30 PM

I get a runtime error 13 type mismatch when I try to run it. I'm thinking about just changing them all by hand, but I am curious about the macros

Chris Davison · Apr 17th, 2002, 03:42 PM

try this in C5 in each sheet

=INDIRECT("'Day "&((RIGHT(CELL("filename",C5),2)*1)-1)&"'!c5")+1

it seems to work for me

_________________
Hope this helps,
Chris

edit........ there's some SINGLE quotes in there amongst the DOUBLE quotes... make sure you squint right up to the screen to get them (or paste it into excel as unicode text)

[ This Message was edited by: Chris Davison on 2002-04-17 14:48 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 09:15 AM	#1
bwoolley New Member Join Date: Apr 2002 Posts: 5	I have a workbook with 84 sheets, one for each day. I cannot figure out how to have excel automatically increment the date in the same cell on each sheet. For example, on day one(sheet 1) I have entered the start date of 3/18/02 into C5. I am trying to figure out how to get day 2 C5 to read 3/19/02.... day 84 to read 6/10/02 with out having to enter them all in manually. Any suggestions?

Apr 16th, 2002, 09:22 AM	#2
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	Try with this code.. i have not checked this code.. as i have no 84 sheets...lol Sub dayincrement() Dim sht As Worksheet Dim i As Integer For Each sht In Worksheets [c2] = Format([c2], "dd/mm/yyyy") [c2] = [c2] + i i = 1 + i Next sht End Sub nishith desai http://www.pexcel.com

Apr 16th, 2002, 09:24 AM	#3
bwoolley New Member Join Date: Apr 2002 Posts: 5	ok, I'm a novice.... where do I enter that code?

Apr 16th, 2002, 11:50 AM	#4
bwoolley New Member Join Date: Apr 2002 Posts: 5	bump can anyone help me here?

Apr 16th, 2002, 03:13 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	are all your sheets called : sheet 1 sheet 2 sheet 3 sheet 4 etc etc ? if they are, we can use this 1,2,3,4,etc to our advantage.... or was that just an example ? edit... I'm off to bed soon... if anyone sees this post maybe they can use the match replace thing in the filename to generate a number with which to increment the date by if the above question is "yes" [ This Message was edited by: Chris Davison on 2002-04-16 14:18 ]

Apr 16th, 2002, 06:49 PM	#6
bwoolley New Member Join Date: Apr 2002 Posts: 5	Actually they are labled day 1, day 2....

Apr 17th, 2002, 08:43 AM	#7
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	Press Alt + F11 and then slect vbaproject in left window... right click it.. and here select insert module.. now double click the module.. and you will see the code window.. paste the code there.. you can go to my site http://www.pexcel.com/download.htm and see how modules are placed. nishith desai

Apr 17th, 2002, 01:30 PM	#9
bwoolley New Member Join Date: Apr 2002 Posts: 5	I get a runtime error 13 type mismatch when I try to run it. I'm thinking about just changing them all by hand, but I am curious about the macros

Apr 17th, 2002, 03:42 PM	#10
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	try this in C5 in each sheet =INDIRECT("'Day "&((RIGHT(CELL("filename",C5),2)*1)-1)&"'!c5")+1 it seems to work for me _________________ Hope this helps, Chris edit........ there's some SINGLE quotes in there amongst the DOUBLE quotes... make sure you squint right up to the screen to get them (or paste it into excel as unicode text) [ This Message was edited by: Chris Davison on 2002-04-17 14:48 ]