+ Reply to Thread
Results 1 to 11 of 11

Display subtotals for one value field/column and hide others?

  1. #1
    Registered User
    Join Date
    08-30-2016
    Location
    Edmonton, Canada
    MS-Off Ver
    2016 64b
    Posts
    92

    Display subtotals for one value field/column and hide others?

    How do you remove subtotals and/or display subtotal for select value fields?

    I have two value fields over two columns. I would like subtotals for only one of the two columns; however, I can't seem to find a way to hide/remove the subtotals for the other field.

    In the attachment, I'd like to display subtotals for the 'Value' column, but not the 'Place' column. How would one do this?


    Help is appreciated
    Attached Files Attached Files
    Last edited by happydays886; 05-26-2017 at 05:16 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Display subtotals for one value field/column and hide others?

    I guess the thing is, you know what you're looking at. No one else has a clue.

    Suggest you post a sample workbook with some typical data.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    08-30-2016
    Location
    Edmonton, Canada
    MS-Off Ver
    2016 64b
    Posts
    92

    Re: Display subtotals for one value field/column and hide others?

    See attached

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Display subtotals for one value field/column and hide others?

    OK, I can't see a way to do what you want. The only way that might work is to select all the totals and set the font and background colours the same. The values will still be there but not visible. Maybe use a macro to format them all otherwise it would be tedious.

  5. #5
    Registered User
    Join Date
    08-30-2016
    Location
    Edmonton, Canada
    MS-Off Ver
    2016 64b
    Posts
    92

    Re: Display subtotals for one value field/column and hide others?

    Is there a VBA script that could accomplish this?

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Display subtotals for one value field/column and hide others?

    Based on information found here: http://peltiertech.com/referencing-p...ranges-in-vba/

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-30-2016
    Location
    Edmonton, Canada
    MS-Off Ver
    2016 64b
    Posts
    92

    Re: Display subtotals for one value field/column and hide others?

    I can't seem to get this to work when I carry it over to the workbook:

    Please Login or Register  to view this content.

    - Error 1004 Run-time

    The pivot table is named "ptEmployees"
    Column of interest is "Sum of Hours"
    worksheet is "Model"

    I get it to work in the 'test' file, but there's something just not working right here and I can't troubleshoot this.
    Last edited by happydays886; 05-30-2017 at 12:38 PM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Display subtotals for one value field/column and hide others?

    ... and I can't troubleshoot this.
    And neither can I unless I can see it

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Display subtotals for one value field/column and hide others?

    Attached is a modified version of the original test file with the Sheet name, Pivot Table name and Field name adjusted to reflect your updated code.


    By the way, Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    Attached Files Attached Files

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Display subtotals for one value field/column and hide others?

    Is this now resolved?

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Display subtotals for one value field/column and hide others?

    Hi,

    You can use conditional formatting to apply a custom number format code ;;; to the subtotal cells for Place by location. Attached is your file with the formatting applied.
    Attached Files Attached Files
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ 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. Insert Subtotals Function - Display Text Field Not Summed - Excel
    By amccreight in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-20-2015, 02:34 PM
  2. [SOLVED] Button to display daily view, if data is blank hide the column
    By batchy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2014, 06:57 AM
  3. Replies: 0
    Last Post: 03-02-2013, 08:15 AM
  4. [SOLVED] Conditionally Sum Values in Column and Display Sum in Form Field
    By Southfish in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-22-2012, 08:14 PM
  5. Insert subtotals function - display text field not summed
    By mystic342005 in forum Excel General
    Replies: 1
    Last Post: 06-12-2010, 03:01 AM
  6. way to hide the column, row and data field in a pivot table
    By Richard Flame in forum Excel General
    Replies: 0
    Last Post: 06-05-2007, 03:16 PM
  7. [SOLVED] Pivot Table - how to display PT Row field in column to right of ta
    By Dennis in forum Excel General
    Replies: 3
    Last Post: 02-25-2006, 05:15 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