1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel monthly updates

Discussion in 'Business Applications' started by russ1162, Mar 20, 2017.

Advertisement
  1. russ1162

    russ1162 Thread Starter

    Joined:
    Feb 11, 2002
    Messages:
    73
    hey,

    How do i automatically update the dates on the spreadsheets of excel? I have 12 spreadsheet and want to start on the middle spreadsheet with one cell having January 2017 as the date. I am not talking about the tabs at all. So each spreadsheet would have a cell with the date as just the month and year on them. If I put January in the middle spreadsheet on a cell I would like the spreadsheet to read as follows: October 2016, November 2016. December 2016, January 2017, February 2017, March 2017 and so forth. When i change the date in that cell I would like to have the next spreadsheet have the cell change automatically.

    Thanks,

    Russ
     
  2. Sponsor

  3. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    62,271
    if you put the
    1/1/16 into a cell and make sure it accepts as a date
    then you can change the format
    by right clicking on the cell and then choosing
    custom format
    MMMM YYYY

    then in the other cells you can use a formula

    lets assume you have A1 with 1/1/16 and you want B1,C1,D1 etc to show as you mentioned
    October 2016, November 2016. December 2016, January 2017, February 2017, March 2017

    in Cell B1 put
    ==DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
    and copy across to C1, D1 etc

    see attached
     

    Attached Files:

  4. russ1162

    russ1162 Thread Starter

    Joined:
    Feb 11, 2002
    Messages:
    73
    I would like to update any cell not just the first one. Plus I want there to be 12 spreadsheet with a date in one cell of each spreadsheet that would automatically update when one spreadsheet is changed.
     
  5. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    62,271
    i'm not sure I fully understand what you are after , and you may need VBA , which is not my area of expertise.
    These 12 spreadsheets are they workbooks or worksheets
    Workbook - is a separate file thats saved with a different name , and can contain many worksheets
    Worksheet - are separate tabs within the workbook
     
  6. russ1162

    russ1162 Thread Starter

    Joined:
    Feb 11, 2002
    Messages:
    73
    I would like 12 worksheet with the Month and year in each worksheet. If i change one month and year in a worksheet I would like the rest of the worksheets to advance the months and years. Thank,

    Russ
     
  7. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    62,271
    all the sheets need to reference the one cell you are going to change
     
  8. russ1162

    russ1162 Thread Starter

    Joined:
    Feb 11, 2002
    Messages:
    73
    how do i do that?
     
  9. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    62,271
    assuming the date you want to use as reference
    sheet1
    cell A1


    =DATE(YEAR(sheet1!A1),MONTH(sheet1!A1),DAY(sheet1!A1))

    and the you can add any days or month you want
     
  10. russ1162

    russ1162 Thread Starter

    Joined:
    Feb 11, 2002
    Messages:
    73
    I am confused at when I put this Formula in the cell B4 sheet 1 why is excel asking for a file name?
    I think we are almost there to what I need. Plus how dost the auto update work once I get this working? Plus do I put this formula in cell b4 sheet 1 or do I just put a date in there?
     
  11. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    62,271
    you have not correctly put the formula into the cell
    its cannot recognise your sheet name and so assumes its a filename

    maybe load a sample file here - so we can see what you are doing
    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1187173