Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Dates incremented across sheets

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    ni****h desai
    http://www.pexcel.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    **bump**

    can anyone help me here?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    ni****h desai

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    ni****h desai

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •