+ Reply to Thread
Results 1 to 5 of 5

Subtotal for hidden sheets

  1. #1
    Registered User
    Join Date
    02-27-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    34

    Subtotal for hidden sheets

    Hi,

    I want to sum several cells on different sheets, but only include the cells if the worksheet is visible.

    Subtotal 109 can be used to exclude cells when row or column is hidden, but how to make this work when the entire sheet is hidden?

    / Kim
    Last edited by kimrh; 08-06-2015 at 11:17 AM.

  2. #2
    Forum Contributor
    Join Date
    07-10-2012
    Location
    Bangladesh
    MS-Off Ver
    Excel 2007
    Posts
    129

    Re: Subtotal for hidden sheets

    if you have hidden sheet name then use indirect function keeeping same as wokingsheet

  3. #3
    Registered User
    Join Date
    02-27-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    34

    Re: Subtotal for hidden sheets

    Hi,

    Thanks for helping out, but unfortunately I'm not sure if I understand what you mean.

    / Kim

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Subtotal for hidden sheets

    if you add a blank worksheet at the start and end of your data sheets, and call them Start and End, then when you enter your calcs, you could just enter =sum('start:End'!C12) or whatever your references are. this way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though

    Pull the sheets out of that range before you hide them
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    02-27-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    34

    Re: Subtotal for hidden sheets

    Think this might work if I adjust the macro to move the sheets around.

    Thanks!

    / Kim

+ 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] Format all cells in all sheets to Protection Hidden on visible and hidden tabs
    By DeRo22 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-28-2014, 03:17 PM
  2. [SOLVED] trying to access the excel sheets/Tabs in the hidden/very hidden mode through hyperlinks
    By Kiran Kurapati in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-30-2013, 06:50 AM
  3. Subtotal and hidden columns
    By Anat in forum Excel General
    Replies: 0
    Last Post: 04-18-2006, 07:45 PM
  4. [SOLVED] using SUBTOTAL() on rows that have been hidden
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] using SUBTOTAL() on rows that have been hidden
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  6. [SOLVED] using SUBTOTAL() on rows that have been hidden
    By doco in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  7. [SOLVED] using SUBTOTAL() on rows that have been hidden
    By doco in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. [SOLVED] using SUBTOTAL() on rows that have been hidden
    By doco in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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