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.

Solved Excel monthly updates

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

Thread Status:
Not open for further replies.
Advertisement
  1. russ1162

    russ1162 Thread Starter

    Joined:
    Feb 11, 2002
    Messages:
    83
    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:
    63,325
    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:
    83
    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:
    63,325
    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:
    83
    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:
    63,325
    all the sheets need to reference the one cell you are going to change
     
  8. russ1162

    russ1162 Thread Starter

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

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    63,325
    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:
    83
    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:
    63,325
    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. russ1162

    russ1162 Thread Starter

    Joined:
    Feb 11, 2002
    Messages:
    83
    How do i go to one tab and change the date on the worksheet then have next worksheet change the date automatically?
    Next I will I will want to know if i can do this with the tabs too?

    Thanks Russell
     

    Attached Files:

  13. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    63,325
    i have setup a formula in each sheet
    =DATE(YEAR('Jan 17'!B4),MONTH('Jan 17'!B4)+1,DAY(1))
    and you can add a month to the cell in Jan

    so if you change Jan sheet cell B4
    all other sheets will add 1 month to that date
    the day() i set as 1
    if you wanted the same date as JAN!B4
    =DATE(YEAR('Jan 17'!B4),MONTH('Jan 17'!B4)+1,DAY('Jan 17'!B4)))

    But if you change the date in Jan - then the sheet names may not be correct

    So i'm not really show what the application of this formula is for and if its the correct approach

    anyway
    heres the example update
     

    Attached Files:

  14. 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...
Thread Status:
Not open for further replies.

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