+ Reply to Thread
Results 1 to 11 of 11

Formula for multiple sheets

  1. #1
    Registered User
    Join Date
    04-23-2018
    Location
    Alabama
    MS-Off Ver
    2010
    Posts
    5

    Formula for multiple sheets

    Hello, I am trying to build a workbook that is 52 sheets (one per week of the year). One requirement is that there be a "last 4 weeks" cell on each sheet after week 4. I am curious if there is a formula that will add a assigned cell for the last 3 sheets, that would be pastable as to not have to do the formula for all 48 pages. For example, net sales will automatically add the last 3 weeks and current sheet to provide a 4 week total. I can build a formula for this calculation but more looking for if there is an option for a sheet basis.
    Attached Images Attached Images
    Last edited by acleveland38; 04-23-2018 at 02:05 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formula for multiple sheets

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    04-23-2018
    Location
    Alabama
    MS-Off Ver
    2010
    Posts
    5

    Re: Formula for multiple sheets

    Here is a sample workbook. Thanks
    Attached Files Attached Files

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

    Re: Formula for multiple sheets

    You need to ensure that the sheet names all have the same format - your "WEEK4 " needs to be amended to suit the others in your example file.

    Then, suppose you use a particular cell in every sheet to record the week number - for example, put 1 in cell A1 of WEEK 1, then 2 in A1 of WEEK 2, and 3 in A1 of WEEK 3, and so on, and then you could have this formula in cell C14 of WEEK 4:

    =INDIRECT("'WEEK "&A1-3&"'!C10")+INDIRECT("'WEEK "&A1-2&"'!C10")+INDIRECT("'WEEK "&A1-1&"'!C10")+C10

    and you can copy this into cell C14 of WEEK 5.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    04-23-2018
    Location
    Alabama
    MS-Off Ver
    2010
    Posts
    5
    Yes I believe that would work. Thank you so much gonna save me a lot of time being able to use that formula.

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

    Re: Formula for multiple sheets

    Glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  7. #7
    Registered User
    Join Date
    04-23-2018
    Location
    Alabama
    MS-Off Ver
    2010
    Posts
    5

    Re: Formula for multiple sheets

    Thanks Pete! I used the formula in the example workbook I made. It works for the first 4 pages. When I tried it on week 5 it throws a #REF error. If I change A1 to 4 it works again but every other number throws the error. Any ideas on what could cause this? I attached the file for viewing.
    Attached Files Attached Files

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Formula for multiple sheets

    There's a space after the worksheet name. "WEEK 4 ".

    Delete that space and the formula will work as expected.

  9. #9
    Registered User
    Join Date
    04-23-2018
    Location
    Alabama
    MS-Off Ver
    2010
    Posts
    5

    Re: Formula for multiple sheets

    Thanks! You guys are awesome!

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Formula for multiple sheets

    Happy to help. Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

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

    Re: Formula for multiple sheets

    The sheet name you have used for Week 4 is actually "WEEK 4 ", i.e. with a space after the 4 (difficult to see). You need to edit the sheet name to get rid of the trailing space, then the formula will work okay.

    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. [SOLVED] What's the formula to return a value with multiple occurances in multiple sheets?
    By dkbrawl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2017, 12:59 PM
  2. Formula to sum cells across multiple sheets or future sheets
    By dLhx in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-13-2017, 02:46 PM
  3. Replies: 1
    Last Post: 02-12-2014, 01:49 AM
  4. Replies: 12
    Last Post: 02-10-2014, 11:59 AM
  5. Replies: 0
    Last Post: 03-13-2013, 12:25 PM
  6. Replies: 0
    Last Post: 11-08-2012, 01:07 PM
  7. Merge multiple excels with multiple sheets in a master excel with multiple sheets
    By marchi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2012, 10:37 AM

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