Results 1 to 9 of 9

Thread: Sum the same cell across multiple sheets - variable number of sheets

  1. #1
    Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0

    Sum the same cell across multiple sheets - variable number of sheets

    Good Day,

    I have a typical set of month sheets (labelled: Jan Total... Feb Total... etc.) and a "YTD" sheet. All month sheets have identical layouts. The YTD sheet also has an identical layout.

    Before each of the month sheets I have to create a variable (dynamic) number of sheets from a template (identical layout to the month sheets) that need to have their data consolidated in the monthly sheets.

    i.e. before Jan Total I will have 4 sheets named HM1, HM2, HM3, HM1; before February I will have 3 sheets HM5, HM6, HM7, etc. Another user will have the same workbook but different number of sheets (named differently) before the monthly total, i.e HD27, HD28, HD29, HD30, HD31, HD35 before January...

    What would be the easiest way to define for each month from which sheets to look up the data from?

    Can the formulas like SUM(HM1:HM4!A1) be made dynamic in the monthly sheets, and change based on the sheets it looks up the data from?

    Thanking you in advance for your insight.

    Rob

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    Hi Rob,

    Welcome to board !

    First question why you use different sheet for different month ? Don't you use a single sheet for all data by adding a column for month ? which will make your analysis easy.

    Can you attach a sample workbook with expected results ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    Hello

    I tried to upload the file but is larger than 102kb and it would not allow me.
    For an easy analogy, the WS containing the data that need to be summed in the monthly WS are the weeks of the year. My code is HM so I would have WS named HM1, HM2, HM 3, HM 4 for January. Another month I might need to divide the week into 2 (having 2 WS for one week), for example HM15, HM16, .....HM23 to be summed in June. Some months might need to combine 2 weeks into 1, then I would have only 2 sheets data to be added in the Monthly WS. What I need is to be able to tell on the monthly sheet the name & number of the sheets that predate the month and have a formula like SUM(HM1:HM4!A1) become dynamic based on these parameter[s].

    Thanks

    Rob

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    you don't need to upload all the data. keep first few rows of data and delete rest. wanted to see the format of each sheet.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    Hello

    Please see attached. I might have answered my own question by adding a "helper" WS before the 1st & last week of the month for formulas to update. " =SUM('JAN START:JAN END'!C5) "
    Not the most elegant solution, but it works.
    I am still in dire need of help with some of the VBA code for this WB. Do I need to start a new thread or just post the code on this one?
    Again, very much appreciate your help.

    Thanks

    Rob
    Attached Files Attached Files

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    Quote Originally Posted by ROBJ View Post
    Hello

    Please see attached. I might have answered my own question by adding a "helper" WS before the 1st & last week of the month for formulas to update. " =SUM('JAN START:JAN END'!C5) "
    Not the most elegant solution, but it works.

    Thanks

    Rob
    It's an elegant solution ! Usually we propose this way to handle 3d formulas.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    Good Day,

    How can the INDIRECT formula be used for the formula in cell C5 ( =SUM('JAN START:JAN END'!C5) ) to update automatically based on a drop down menu in cell A1 which allows to chose the month?
    i.e. I would like the monthly sheet for February to update the 3D formulas automatically after choosing FEB from the drop down?

    Appreciate your help.

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    327
    Rep Power
    6
    Don't split data into separate worksheets.
    Use an intelligent table and a pivottable instead.

Similar Threads

  1. Replies: 1
    Last Post: 06-11-2015, 09:33 AM
  2. Replies: 1
    Last Post: 09-03-2013, 10:34 PM
  3. Replies: 3
    Last Post: 08-10-2013, 10:12 PM
  4. Replies: 2
    Last Post: 04-15-2013, 02:53 PM
  5. Vlookup - Multiple Sheets
    By Suhail in forum Excel Help
    Replies: 3
    Last Post: 01-30-2013, 07:17 PM

Posting Permissions

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