+ Reply to Thread
Results 1 to 18 of 18

How to create automated new entries in a pivot table

  1. #1
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    How to create automated new entries in a pivot table

    Dear Excel forum members

    I would like to take this opportunity and thank you all for your competence.

    I am a novice in Excel and still learning.

    Now to my dilema:

    I have attached an Excel file, called "Lena" were I have a created a pivot table through some help with some help from Richard. This can be found in sheet name "pivot table". The data which can be seen on that page dervies from sheetname "data". From sheet name "data" with some help, I did macro record, if you look at the top (Cell B3, C3, D3 etc), "Name, project Name, hours" you can fill out information in these cells (The names of people comes from a sheet name called "variable"), when you are done, you can click at the top "Click here to add information". Once you do this, that new information will be populated below and at the same time be seen on the sheet name "Pivot Table".

    When you are at the Pivot table, to the right, you can see month and each week, for instance January 2017 week 1, 2 and so forth. Under each week I made a formula of total amounts of hours spent in a certain project, this information can be found in sheet name "1" which is project name IKEA (cell A5).

    My question is, when I fill out information in sheet name "data", I want it to automatically create a new sheet, a sheet name with that project name (from data sheet) and the people involved in that project as you can see in sheet name 1 which i manually created. Then at the same time, the amount of hours filled in this sheet, should be automatically shown and summarized in the sheet name, "pivot table"just as I have done manually. Please, I really need your assistance and help.

    Thank you so much for your help and time, I greatly appreciate it

    Best regards,

    Lena
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Hi,

    I have done similar projects in the past and found that it can quickly become very cumbersome.
    With so many similar tabs doing the same thing for one criteria, it become a problem if a change needs to be made.
    Also to populate the each of those individual tabs with formula's to pull the desired data from the Data tab will cause the overall sheet to get progressive slower.
    My suggestion is one "Projekt" Calendar tab to show the desired data by just changing the "Projekt" name from a pull down.

    The attached example is a modified version of your spreadsheet.

    It incorporates PivotTables and Dynamic Named Ranges, such as you were already using.

    Check it out and take it for a spin. Let me know if it is something you would like to work with.

    If you like it we can keep working on it.

    Hope this is helpful.

    Cheers
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear Southward,

    Thank you for your wonderful help and outstanding excel sheet, i really appreciate it I really like calender approach you made, how did you do it?

    One question, in the "data" sheet, how do I fill out the "Year/Week" information? Whenever I try to fill out for instance 1705, it shows up 1904-08-30. How did you manage to get 1704 then add that info? Also, in the "Calendar" tab, it only shows people who have actually filled out the amount of hours, but is there a way to show all the of names that appear in the sheet name "variables", the thing is I also want to see those who dont fill out hours, so I can delegate them hours

    I really appreciate your help my dear friend! You are just awesome

    Best regards,

    Lena!
    Last edited by Lena84; 01-26-2017 at 05:14 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Hi,

    The Year/Week formula is:

    Please Login or Register  to view this content.
    It is entered into your data via the macro you use to add Date/Name/Project Name/Hours to your data.

    All you have to do is enter the data in row 4 as normal. Just skip over cell C4. Once you select the "Click here..." button the formula will be entered with the other data.

    For the Calendar tab, the names mirror the list of names in the Variable tab via simple formulas.

    Attached is the updated version.

    Let me know if you have any issues.

    Cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear southward,

    It is friday, the sun is shining and I just love it

    First and foremost, I really appreciate your help, your are sooooooo awesome

    I went to the "data" sheetname and I followed your instructions. I filled out information in row 4 and skipped c4. That is just awesome that it will show the year and week automatically The thing is, when i then go to the "pivot table" sheet, to check if it fills out the amount of hours during a certain week, a default window appears and says that "Run time error '1004' Application defined or object-definer error: "end" or "debug", now I am confused dont know what's wrong. Also it wont show up the hours put per week in the "pivot table".

    Finally, how do I reference hours put per week from the "data" sheet to appear in the "calender" sheet under respective persons name, and week per hours?

    Thank you advance my dear friend, I really appreciate your engagement and interest in this matter

    Thank you for taking time answering my questions,

    The best and warmest regards,

    Lena!

  6. #6
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Hi,

    Sorry about the complications.

    I didn't realize that the reference cell at the top of the Dynamic Named Ranges (DNR) were moving down a cell every time a new info was added.
    I modified them so that they capture the data correctly now.

    Not sure why the Pivot Table was causing an error. I added a couple of lines of code to the macro that refreshes the Pivot Table. It doesn't appear to be causing an error anymore.

    As for the "calendar" sheet, I think the problem with the DNR's was causing problems with it also. Should work correctly now.

    Let me know if there was something else you wanted the "calendar" to do.

    Let me know if there are any other problems.

    Hope it works for you.

    Cheers
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Hello southward,

    Hope all is well You are so wonderful, if there were 1 million gold stars to add to reputation, I would! You are the best dear!

    I really like the changes you have made, now it will make the whole set up so much more efficient! I love your solution!

    One step left, as you have referenced the amount of hours into the "pivot table" sheet per projectname, I would also want the same methodology to apply in the calender sheet but instead under each project, hours per week under respective persons name. Is that possible to do, and if yes, how? If this is workable then we have a complete list! That's really the last step for it to work :D

    I wish you all the best in life and may all your wishes come true my friend, you are too wonderful :D

    Best and warm regards,

    Lena

  8. #8
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Hi,

    Glad that you are liking the new format so far.

    For the "One step left", are you asking for a "sum" of the hours for each person? If so I added a column to the calendar tab.

    I also played around with a Pivot Table to include both the Project and Persons Name. New tab added is "Namn & Projekt". Also includes two "slicers" to quickly filter data.

    Check it out and let me know if the changes are OK.

    Cheers
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear southward,

    Hope all is well. Hope your weekend has been pleasent so far.

    I have been sick and slowly getting better.

    I took a look at your wonderful excel file and it was wonderful to look at. It made me alittle bit better from my cold

    I sure do like what I see so far my dear friend.

    I will let you know if I have questions again

    I wish you a pleasent sunday my dear friend.

    Have a wonderful day dear southward!

    Best regards

    Lena

  10. #10
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Hope you feel better soon.

    Cheers

  11. #11
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear southward,

    Thank you for those kind words, I simply wish you all the best

    I noticed when I opened the excel and whenever I switch beetween excel tabs, "Namn & Projekt" and any other sheet name, the default window appears: "Run time error '1004': Unable to get the Pivotfields property of the PivotTable class" Then I can choose to "End" or "Debug".

    How to fix this problem?

    Thank you in advance dear friend!

    best and warm regards,

    Lena

  12. #12
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Hi,

    I don't know why it is causing ERROR's. I can't get it to error for me.
    I added some error handling to the macro's that refresh the Pivot Tables.

    Hopefully that will keep it from causing errors for you.

    Let me know if it happens again.

    Cheers
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear Friend,

    I wish you the best in life, thank you so much for your help! Thank you so much for your time, really from the bottom of my heart

    Best and warmest regards,

    Lena!

  14. #14
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Glad I could be of some assistance.

    Cheers

  15. #15
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear Southward,

    Hope all is well my dear friend

    Just one question, when you open up the file, do you also experience that the macros are running alittle slow? If yes, is there a way to avoid this problem? Also, can I share this document so that multiple users can work on the document at the same time?

    Warm regards,

    Lena

  16. #16
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear Southward

    Hope all is well my dear friend and that your week has been good

    Just one question, when you open up the file, do you also experience that the macros are running alittle slow? If yes, is there a way to avoid this problem? Also, can I share this document so that multiple users can work on the document at the same time?

    Warm regards,

    Lena

  17. #17
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: How to create automated new entries in a pivot table

    Hi,

    I am not experiencing any slowness.
    What I have is an example of your data.
    The more data you have the slower it is going to be. There are a lot of formulas. All those formula are going to re-calculate every time you do something.

    You can turn off the Automatic calculations via "Excel Options" / Formulas. Set "Workbook Calculation" to Manual.
    You will have to press the F9 key when ever you want to see updated results.

    I don't know about a spreadsheet being accessed by multiple users.
    Here is a website that give instruction for multiple user access.

    http://smallbusiness.chron.com/make-...ers-71302.html

    Cheers

  18. #18
    Registered User
    Join Date
    01-24-2017
    Location
    Malmö
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: How to create automated new entries in a pivot table

    Dear southward,

    Thank you for your recommendations, I will sure look further into it.

    I wish you a pleasent friday and wonderful weekend

    Best and warm regards

    Lena

+ 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. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  2. [SOLVED] Entries on pivot table removed when table cell = 0
    By lockers in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-23-2014, 12:29 AM
  3. Automated Emails from Excel Pivot table
    By cicapopsi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2013, 05:49 AM
  4. Pivot Table - Automated Data... Learning Purpose
    By way2suresh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2013, 09:16 AM
  5. Budget v Spend Pivot Table - can this be automated?
    By NicNocs in forum Excel General
    Replies: 6
    Last Post: 10-02-2011, 09:18 AM
  6. [SOLVED] Create Pivot Table: Cannot Open Pivot Table Source File
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 01-05-2010, 05:11 AM
  7. pivot table macro buttons to run automated search
    By schueyisking in forum Excel General
    Replies: 0
    Last Post: 08-15-2008, 10:08 AM

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