Sum across worksheets while ignoring hidden worksheets

  • Hello,


    I’m trying to figure out how I can do a SUM formula across worksheets and ONLY count the cells on the worksheets that are visible and ignore the cells when the worksheet is hidden.

    In the attached – the “cover sheet” tab cells B10:B15 are designed to allow users the ability to hide or unhide cells. Straightforward Yes/No selection.


    The formulas in K12:K17 on the same tab count the corresponding cells on all the remaining worksheets F1:F7 (except F2). I’d like this formula to ignore the corresponding cells when the worksheet is hidden.


    Example – I select NO in cell B12 on the Cover Sheet Sheet Tab. This hides the Call Center tab from view. I don’t want any of the data in the call center tab cells F1:F7 to be counted on the Cover Sheet tab.


    I can’t use VBA code as we have users who are using MAC devices –



    Thanks

  • Hello,


    Have you tried to create a named range with only your visible sheets ... not tested ... but it should work ;)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks, Carin,


    I'll admit my skillset in names is probably not as good as it should be. Would names allow the calculation to be dynamic in the sense that hiding and un-hiding a worksheet is selectable by the user.

  • Hello,


    Regarding your test file, a few remarks :


    1. Your six 'Applications' worksheets can hold much simpler formulas in their respective range F1:F7 ...

    a much simpler COUNTIF() formula does the job ...


    2. Your Change event to hide and unhide worksheets can be streamlined and optimized ...


    3. In the attached test file, within your 'Cover Sheet' ... you can select Yes/No in the range B12:B17 :

    This will trigger your event macro but it will also create a named range (located in M12:M17 - called MyList)

    You could obviously hide this Column M, but this list is required by the Array Formulas located in the range K12:K17

    in order to display your expected results ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks Carim,


    This looks really good. One issue that I found is on the cover tab - when I select YES for Session Manager in cell B13 the corresponding worksheet does not appear but when I select YES for Survivability in Cell B12 I get both Survivability and Session Manager worksheets to appear. The calculations in row K are working

  • The main question is to check if all your calculations in Column K do adjust to the ' Hide & Un-Hide ' selection process of your six worksheets ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks. One last question if you don't mind. Attached is the latest copy of the workbook - some of the row numbers changed on the coversheet tab due to formatting and feedback. I updated the code best I can using what you sent. The only thing that I can't get to work is the calculations in column K. I copied your formula over and hit CTRL-Shift-enter to make in an array and did a steer and compare - I can't find the place to update.

  • If I may ... your transposition is really PERFECT ... !!!:)


    Tiny detail ... the worksheets' names in the range A10:A15 have to identical to the names used in the tabs ... ( i.e. without : )


    Attached is the correction ...


    Hope this will help

    :)

  • Forgot you have built your six 'Applications' worksheets ... skipping Row # 2 ... which does impact your Columns F ...

  • Thanks - I was just looking at that - hopefully this will be all. If we end up adding more worksheets - thus more test plan results on the cover sheet, what do I need to modify in the formula in column K?

  • Thanks for your Thanks ..AND for the Like :thumbup:


    Should you expand your list of worksheets ... you should adjust the definition of the Range Name 'MyLIst' , and, within the formula in Column K adjust the total number of sheets to the latest total ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)