+ Reply to Thread
Results 1 to 14 of 14

Manual additions to a Pivot Table

  1. #1
    Registered User
    Join Date
    05-18-2018
    Location
    Waalwijk, Netherlands
    MS-Off Ver
    2016
    Posts
    7

    Question Manual additions to a Pivot Table

    I've exported data from Revit via Dynamo to excel (you won't need any knowledge about those two for this topic) that creates a data dump with 3 (relevant) colums:
    -Panel Type
    -Floor
    -Amount (number of panels)

    From this dump I've made a pivot table with the Panel Type in Rows, the Floor in columns and the Amount in Values.

    What I want to add to it is another column which can show the ordered panels and can be manually adjusted as well as a column showing the difference between the exported number of panels and the mannually added column with ordered panels.

    Now you might wonder "why not just add a column with a basic formula", the issue is that the Pivot table will change, partially because I want to build a template to be used for different projects, on the other simply because a project might be revised and different panel types might be added. Next to that, for the simple reason that I'd like this column to show more to the left in the table.

    I hope someone has any tips on setting this up (if possible). Thanks in advance!

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Manual additions to a Pivot Table

    Attach a sample workbook (not a picture!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    05-18-2018
    Location
    Waalwijk, Netherlands
    MS-Off Ver
    2016
    Posts
    7

    Re: Manual additions to a Pivot Table

    For some reason I can't manage to add a file to this post...
    I'll try to explain at best as I can.

    So there's a pivot table with in the right column a lot of numbers, next to it are two list, the most right one is a list that you should be able to adjust manually. In between those lists there would be another list with the difference between them.

    What I'd like to be "special" about it is that when you add a new row (or rows) to the pivot table I'd like the manually adjustable list to move along with the change.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Manual additions to a Pivot Table

    Pivot Table works with Columns and Content

    maybe try this way:

    To attach an Excel file to your post,
    • desensitize data
    • remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  5. #5
    Registered User
    Join Date
    05-18-2018
    Location
    Waalwijk, Netherlands
    MS-Off Ver
    2016
    Posts
    7

    Re: Manual additions to a Pivot Table

    I mean I did.
    It's just that the button didn't seem to do anything then.
    But it does now, so problem solved I guess.

  6. #6
    Registered User
    Join Date
    05-18-2018
    Location
    Waalwijk, Netherlands
    MS-Off Ver
    2016
    Posts
    7

    Re: Manual additions to a Pivot Table

    I hope it worked now...
    Last edited by PauLtus; 05-24-2018 at 06:39 AM.

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Cool Re: Manual additions to a Pivot Table

    You can use PowerQuery aka Get&Transform, then PivotTable or just PowerQuery result table

  8. #8
    Registered User
    Join Date
    05-18-2018
    Location
    Waalwijk, Netherlands
    MS-Off Ver
    2016
    Posts
    7

    Re: Manual additions to a Pivot Table

    The issue is that I do not want the order list in the data dump.
    Last edited by PauLtus; 05-23-2018 at 08:11 AM. Reason: Meant the complete opposite

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Cool Re: Manual additions to a Pivot Table

    I did what you show.
    so maybe this one
    if not create example with the result what you want to achieve and nothing more. mock them up manually if necessary

  10. #10
    Registered User
    Join Date
    05-18-2018
    Location
    Waalwijk, Netherlands
    MS-Off Ver
    2016
    Posts
    7

    Re: Manual additions to a Pivot Table

    The issue is not that it doesn't look the way I want it to look, hell it actually functions as I want it.
    The issue is when I were to add a new type to the data dump and refresh the pivot table, I'd like the data from the ordered list to "stick" to the type it was already behind.
    I don't want the situation where the user would have to go to another list and alter the data there.

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Manual additions to a Pivot Table

    but you need source of Ordered column aligned to appropriate Type, right?

  12. #12
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Cool Re: Manual additions to a Pivot Table

    try this
    add something to the source table, then refresh green table, then add to the order table (blue) and refresh again (ctrl+shift+F5)

  13. #13
    Registered User
    Join Date
    05-18-2018
    Location
    Waalwijk, Netherlands
    MS-Off Ver
    2016
    Posts
    7

    Re: Manual additions to a Pivot Table

    So uhm.
    The reason I had the order automated is because I want it to be in the order A-Z, I do understand you can avoid some issues by ignoring that, but I want it in the proper order.
    Thing is: I want it to function when you add a piece of data right in between the data dump.

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,750

    Re: Manual additions to a Pivot Table

    so you need PT with Type, Sum and Difference - automated and Table Ordered which order (sort) will be updated automatically from PT but in the same time you want to add any value to this table?

    I think you should look at VBA sub forum

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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