1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Entry and Pivot Table

Discussion in 'Ask an Excel Question' started by Mark Benham, Oct 1, 2014.

  1. Mark Benham

    Mark Benham New Member

    Messages:
    5
    Is there a way of appending a column to a pivot table which allows data to be typed in to it?

    I am downloading a Job Costing ledger in to Excel and analysing project performance using pivot table. This makes it easier for non-users of our accounting system to see project performance. The no. of rows in the Pivot Table changes depending on which Project is selected from the Pivot Filter.

    I also want to collect actual % complete by each activity within the project from the users. It would be good if I could add a data entry column in to the right of the Pivot Table so the end user can enter % complete for each activity and pass that info back to the downloaded table.
    Mominul Islam likes this.
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,282
    In short, no. PivotTable data flows one way, from source to the Table. Users will need to either find actual entry in data table and make change there, or you need to create some fancy coding to figure out where data needs to go. Problem I initially see is that with a PT, 1 row may be a summation of many rows in data...so which row(s) should comment be applied to?
  3. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    I'd be interested in looking at this. Mark - can you upload a sample file that outlines a little more about what your source data looks like, what your pivot looks like, and how the change to the pivot would relate to the Source Data?
  4. Mark Benham

    Mark Benham New Member

    Messages:
    5
    Thanks for your responses. I suspect the answer is No but worth asking. I have attached example of sheet - I have removed the ODBC links but essentially project info is sourced from Accounting system on 2nd and 3rd sheets then presented in Pivot Table on first.

    Challenge is to then get feedback from Proj Mgrs - using same report but updated by them would be good but consolidating back requires all the line info in the Pivot table - Job No., Extra - to remain associated with the data they input - which is lost as soon as a different Pivot Filter is used or the filter is removed.

    Attached Files:

  5. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    Luke M and NARAYANK991 like this.
  6. Mark Benham

    Mark Benham New Member

    Messages:
    5
    Sage CRE 300 (Timberline) - ODBC link.

    The thread looks interesting - wasn't aware of this function. Thanks
  7. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    Mark, in your sample data, your PivotTable doesn't actually aggregate anything, but just reports the numbers. i.e. the source table has 30 rows, and the pivot table would also have 30 rows if you removed the filter from Job.

    This is because the combination of the fields in your pivot are unique. Is this likely to be the case in practice?
  8. bobhc

    bobhc Excel Ninja

    Messages:
    3,362
    Update your raw data and let the PT update its fields, PT is only realy designed to display data as the author wants others to see it. Why would you want end users to imput into the PT, a novice could do a lot of damage, set up a blank worksheet that is linked to the raw data table so that they can enter their data without screwing evrything you have done.


    .
  9. Mark Benham

    Mark Benham New Member

    Messages:
    5
    Jeffrey - yes, in this report I do not aggregate data - I use the table to apply calcs to the downloaded data before using the pt to select the relevant lines of data by job.
  10. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    I think I've found a way to do this.

    Bobhc...the intent here is really to let users attach notes to PivotTables. That is something that is sorely missing from Excel. I think I've found a way to actually do this.
  11. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    Okay, this is a starting point. Open the attached file, and in the PivotTable write something in the Notes column. You should find that you can filter the pivot, unfilter, and that info will still be there. And you can delete it.

    At last...a way to store information in PivotTables!

    Still need to iron some kinks out. But I've been after something like this for years.

    Attached Files:

    Thomas Kuriakose likes this.
  12. bobhc

    bobhc Excel Ninja

    Messages:
    3,362
    @jeffreyweir

    Notes attached to a PT, jeeeeeze, next users will want coloured cells and text.
    Luke M likes this.
  13. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    BobHC:
    There's actually quite a few software packages that let users do this - make changes in a pivot, and let the data get updated back in the cube. Governed by appropriate security of course. And as per the screenshot above, Excel itself lets you do this with OLAP cubes, if you set it up.

    I've always wanted a way to let users do stuff like record their own information right there in a pivot. One example might be where say you have some sales information that gets pulled out of a database, and you want to let budget holders play around with forecasts against that actual sales info, and then let them run some graphs off that.

    I've had to play around with all sorts of complicated workarounds with this before. But maybe this crazy solution I've found will get around that.

    I'm off to bed now. Nearly 2am here!
  14. bobhc

    bobhc Excel Ninja

    Messages:
    3,362
    @jeffreyweir

    Less playing more working on your book.
  15. Luke M

    Luke M Excel Ninja

    Messages:
    9,282
    Well I'll be...nope, never saw that before. Just got 2010 recently, so love learning about these new features. Will have to play around with this more. :)
  16. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    yeah, guilty as charged.

    @Luke M: so how 'bout the new feature I just invented in the book I uploaded?
  17. Luke M

    Luke M Excel Ninja

    Messages:
    9,282
    Nice job @jeffreyweir
    Looks like changing the PT is just changing the labels, not the source data underneath?

    Also, looking at your code, a little mystified about the Dec2Bin usage. Can you explain why we do this, rather than just setting caption = " ", or some other blank?

    EDIT: Ah, I see now reason for the Dec2Bin. Apparently need to have a unique note, and that function let you generate just such a thing. Good to know also when I tried to comment multiple lines with same note, such as "Complete"
  18. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    Good deduction, Mr Watson. Yes, it just changes the labels, and not the underlying data. DEC2BIN is required because it allows you to use a shorter number of blanks in the event that users make notes in thousands of fields.

    Next step is to allow writeback to the actual notes field itself.
  19. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    I'll fix this, so that it adds the Dec2Bin bit at the end, ensuring that the notes are always unique.
    Luke M likes this.
  20. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    So here's a more fleshed out proof of concept.
    You can change or delete notes in the Notes column of the PivotTable. You can refresh or change the structure of the Pivot, and those notes will still be there.

    It would be relatively simple for me to amend the code to actually update the 'Notes' category in the PivotTable source data too i.e. update it in the 'Open Job Data' table. (Currently it doesn't do this).

    So that's a proof-of-concept that's applicable to RowFields, Pagefields, or ColumnFields. What about Values fields aka Data Fields?

    Glad you asked.

    If you try to change something in a Values field, you get this: 3-10-2014 2-30-40 p-m-.gif
    But you can actually turn that warning off, and let the user overtype the value, by setting the PivotTable's .EnableDataValueEditing property to TRUE.

    This lets the user temporarily overwrite something in a Data field, as I've done below.
    3-10-2014 2-45-11 p-m-.gif

    Note that this isn't reflected in totals, and when the PivotTable is refreshed, the temporary figure is overwritten. But before then, we could do something with that figure, such as store it somewhere, or even overwrite the source data with the new figure.


    Note that if the field aggregates a whole bunch of numbers - as is usually the case with PivotTables - you'd have to amend all the separate amounts so that they add to the new total. But that's trivial...you could just adjust all the separate figures by a weighted average so that they arrive at the new figure. Or you could store the new figure in an 'Override' column, that you could then bring into the PivotTable itself...so that the old figures are still intact and un-tampered with, but so that your new calculations use the new figure.

    Even better still, you could let the user double-click on a Values cell - which triggers the Drilldown functionality and spits out a sheet of all the transactions that make up that total - and then let the user choose which ones they want to amend, and then let them replace the source data with their amended items by swapping out the relevant lines in the original data table with the lines that were just amended.

    I'm going to build all this into the PivotTable addin I'm building.

    Any comments, anyone. Other than 'you shouldn't allow users to change source data'. Because there's fundamentally no difference between them being allowed to change it directly in the pivot vs in the back-end table. So long as they have permission to make the appropriate changes, it matters not where they do it.

    Attached Files:

    Last edited: Oct 3, 2014
  21. Mark Benham

    Mark Benham New Member

    Messages:
    5
    OK - now you are showing off!

    I'm impressed (albeit unable to fully follow what you have done). Problem is that I'm not sure I can properly maintain this once handed over to users to break.
  22. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    Fair call. As you'll see if you click on my avatar, my motto is:
    Need something 100% fool proof? Easy: kill 100% of the fools.
    The first part of my evil genius proof of concept might not get them in any trouble. i.e. the stuff I've done to date.

    Maybe have a play around, give it to a coulpe of test dummies, and see if it's robust enough for your needs.

    Otherwise really your only option is to not let users play with the pivots, which kind of defeats the purpose.

    I can explain what I've done, so that you can add it to the documentation. But I'm first interested in whether this is robust and suits your needs.

    Gotta dash. Talk to you soon.
  23. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Experts

    Thanks for sharing the above information with us.. I implemented the above code in a pivot table and I am able to add a note/comment in the pivot table..

    I Checked the above example and got to know that you have used the formula "=ROW()" under the "Notes" column in the RAW data. Could you please lemme know how come the value of the "Notes" column is not refleting in the pivot table...I find it blank in the pivot table to add comments..I want the same functionality in my Pivot as well..

    Please let me know what to do to make the Notes column blank in the pivot table.

    Regards,
    Jaggi
  24. Jagdev Singh

    Jagdev Singh Active Member

    Messages:
    601
    Hi Experts

    I performed so many test on the above code from today morning and find a limitation to the code.

    If you use the word "Pending" for activity 1 as per the sample file, you cannot use the same word "Pending" for anyother activity (2,3,4,5...). In case if a user want to assign one word for the entries in the pivot table, it is hard for him/her with the current set of codes.

    Regards,
    Jaggi
  25. jeffreyweir

    jeffreyweir Active Member

    Messages:
    1,086
    You can get around that by adding a space for the 2nd instance, adding two spaces for the 3nd instance, etc.

    When I get more time, I'll see if I can program that to happen automatically, because I want to use this in my forthcoming book, Excel for Superheroes and Evil Geniuses
    Luke M likes this.

Share This Page