+ Reply to Thread
Results 1 to 7 of 7

Formula to look at sheets with changing names

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Formula to look at sheets with changing names

    Hi,

    I have a file where I add a worksheet each month, named as the current month, e.g. Jun15.

    What I'm wanting to do is have a sheet that calculates movements from the latest month and previous month. So this month, my formula would look at Jun15 and May15 worksheets. However, once we get to July and I add a new worksheet, I want my formula to look on sheets Jul15 and Jun15.

    I can't work out how to do this. I know that sheets can be reffered to by number in VBA, but I don't know how I'd set that up either, so I'm at a bit of a loss on how to do this. I mention this as the new month is always the foremost sheet, so the latest would be sheet 1, previous month sheet 2 etc.

    Many thanks

  2. #2
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Formula to look at sheets with changing names

    Could you upload a sanitised sample workbook.
    Include any relevant sheets.

    Windy

  3. #3
    Forum Contributor
    Join Date
    03-17-2005
    Location
    Canterbury, Kent
    MS-Off Ver
    2003, 2010
    Posts
    285

    Re: Formula to look at sheets with changing names

    Could you upload a sanitised sample workbook.
    Include any relevant sheets.

    Windy

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,736

    Re: Formula to look at sheets with changing names

    You can use INDIRECT to do this, along the lines of:

    =INDIRECT("'"&TEXT(TODAY(),"mmm-yy")&"'!B10")-INDIRECT("'"&TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1),"mmm-yy")&"'!B10")

    This will look at B10 in the current month's sheet and subtract from it the contents of B10 in the previous month's sheet.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    10-15-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula to look at sheets with changing names

    Here's a really simple example.

    I've manually made the formulas on the movement sheet, but I want to avoid changing them next month when I add a Jul15 sheet.

    Example.xlsx

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,736

    Re: Formula to look at sheets with changing names

    Assuming your data is always in the same layout on those monthly sheets (i.e. Dogs is always on row 2), then it would be a bit more flexible if you entered the date of the current month in D1 rather than rely on the TODAY() function, as you might not create the July sheet until, say, 3rd July. So, in your example put 1/06/2015 in D1, then you can have this formula in D2:

    =INDIRECT("'"&TEXT(D$1,"mmmyy")&"'!B"&ROWS($1:2))-INDIRECT("'"&TEXT(D$1-1,"mmmyy")&"'!B"&ROWS($1:2))

    which can be copied into D3. Then when you have created a sheet for July, just change the date in D1 to 1/07/2015 - this also makes it more obvious which months you are comparing.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    10-15-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5
    Perfect, that's exactly what I was trying to get to

    Thank you very much!

    Quote Originally Posted by Pete_UK View Post
    Assuming your data is always in the same layout on those monthly sheets (i.e. Dogs is always on row 2), then it would be a bit more flexible if you entered the date of the current month in D1 rather than rely on the TODAY() function, as you might not create the July sheet until, say, 3rd July. So, in your example put 1/06/2015 in D1, then you can have this formula in D2:

    =INDIRECT("'"&TEXT(D$1,"mmmyy")&"'!B"&ROWS($1:2))-INDIRECT("'"&TEXT(D$1-1,"mmmyy")&"'!B"&ROWS($1:2))

    which can be copied into D3. Then when you have created a sheet for July, just change the date in D1 to 1/07/2015 - this also makes it more obvious which months you are comparing.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula across several sheets with variable names
    By LeanAccountant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2014, 11:48 AM
  2. Replies: 2
    Last Post: 08-20-2013, 03:02 PM
  3. Replies: 0
    Last Post: 03-13-2013, 12:25 PM
  4. Changing Sheet Names Based on # Values in Names
    By Mordred in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-01-2010, 03:34 PM
  5. Changing VB Component Names to match Worksheet names using VBE
    By Philip in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2005, 01:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1