Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    Please see attached Workbook.
    In sheet "Data" I have the data of four branches, and the weekly apples and oranges sales of each branch. Additionally I have columns calculating percentage of each branch total weekly sales was in apples and oranges.
    It’s a simple case, nothing complicated. The format of every columns was done according to the Excel rules.
    The intention of the work is to add, at the end of every week, the new values, and have the Pivot Table [PT] (see sheets “Pivot” and its copy “Pivot 2”) updated.
    BTW, for cells showing the data of fore-coming weeks, I had to “create” fudge values (0.01), so the PT would proceed; otherwise I got errors for empty cells.
    This PT exercise gave me several surprises:
    First, that the created PT doesn’t keep the format of the (already formatted original) data.
    OK, I can live with that, I re-formatted the first week (3 Sept 2010), and copied that formatting into subsequent others. In table options I clicked “preserve formatting”
    Second when I wanted to test if the weekly update would work, I LOST ALL PERCENTAGE CELL FORMATS!!
    Verify: In sheet “Data” write cell C64, write any value; and afterwards in PT, click in the ! exclamation mark icon of toolbar for updating.
    I don’t know if it’s something I’m doing wrong (I am NOT experienced in PTs), if that's the case, I will very much appreciate your correction(s) and guidance.
    Thanks in advance

    Daniel Rozenberg`
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Daniel - Take a look at tab labeled Pivot 3. Is that what you are looking for? Also look at the tab with my notes.

    There are other things that can be done with the PT's.

    Tim
    Attached Files Attached Files

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    See attached. I added a dynamic named range that will expand as you add data and set it as the pivot's source data. I also right-clicked your % fields in the pivot table, chose Field Settings, then clicked the Number... button and assigned a percentage format.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi Tim & Rory;

    Thank you VERY MUCH for your replies and assistance.

    Your approach, Tim, is the most immediate, and it is likely the one I will use; at least initially.

    Your approach, Rory, requires about some more effort from my part.

    I confess that I NEVER USED dynamic named ranges before, so I went to Help in the Excel menu, searched for links, and now am reading about. Thank you for giving me the opportunity of learning something new.

    Best regards.

    Daniel Rozenberg.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi Tim & Rory;

    Thank you VERY MUCH for your replies and assistance.

    Your approach, Tim, is the most immediate, and it is likely the one I will use; at least initially.

    Your approach, Rory, requires about some more effort from my part.

    I confess that I NEVER USED dynamic named ranges before, so I went to Help in the Excel menu, searched for links, and now am reading about. Thank you for giving me the opportunity of learning something new.

    Best regards.

Posting Permissions

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