+ Reply to Thread
Results 1 to 10 of 10

Help needed to create a pivot table..

  1. #1
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    14

    Help needed to create a pivot table..

    I am new to Pivot tables and I have watched an online video and it "looks" easy but I am struggling to get one to work for the attached file.

    I am concerned that maybe what I am trying to do is too complex, meaning, that a Pivot table cannot work on this size of a table?

    My goal is to be able to do two things.

    a) Calculate monthly revenues and costs relating to these specific staff, by person name and by Position, eg Partners, Counsel, Associates etc. for each cost item, eg monthly salary, health insurance, taxes etc

    b) the attached assumes some staff may join in the future, can a pivot table work this way? That i can add in names and new staff terms and will the pivot table auto update?

    Thanks for advice on how best to structure this for pivots to work and make it easier to process the data quickly rather than manual excel?
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007, 2013, 2016
    Posts
    67

    Re: Help needed to create a pivot table..

    Hi Carina

    1) I noticed that you have an additional column in the middle (Column B). Once I removed that, I'm able to insert a pivot table for the data. Usually, I'm working on much bigger data than yours. So, I think your data is fine. I would suggest you to create a sample worksheet to show what kind of data you have and manually enter the desire results on another sheet. It would be easier for others to assist you with a solution.

    2) I think you can use the Table function. Just select all the data then press Ctrl + T (I did on your file). Whenever you add new comer below the last staff, it will auto extended the table further down.

    Refer attached for your easy understanding
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    14

    Re: Help needed to create a pivot table..

    Quote Originally Posted by Hikari View Post
    Hi Carina

    1) I noticed that you have an additional column in the middle (Column B). Once I removed that, I'm able to insert a pivot table for the data. Usually, I'm working on much bigger data than yours. So, I think your data is fine. I would suggest you to create a sample worksheet to show what kind of data you have and manually enter the desire results on another sheet. It would be easier for others to assist you with a solution.

    2) I think you can use the Table function. Just select all the data then press Ctrl + T (I did on your file). Whenever you add new comer below the last staff, it will auto extended the table further down.

    Refer attached for your easy understanding
    Ah, thank you for seeing that extra Column. I thought I was going crazy as I couldnt understand what was wrong ! So I did what you suggested and I attach the "ideal output" I am trying to achieve quickly and wonder is a Pivot table going to be able to generate this for me? What I am unclear about is where for example there is complexity of someone starting mid month etc, how is all of that calculated? So for each section of outputs I include what I think needs calculating by the Pivot table for that section but I am not sure how to go about generating all of this. As you can see, I am effectively generating monthly data, summarised by Position so I can then do a high level P&L.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Help needed to create a pivot table..

    Here is a formula based proposal modeled on the Ideal Output sheet.
    For headcount:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For Billing Target:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For Salary and Income Tax:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Etc. The subsequent formulas are the headcount formula with references to the appropriate table column(s) appended.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    14

    Re: Help needed to create a pivot table..

    Thank you JeteMc for taking the time to help me with this.

    I am unable to get the Income Tax one to work? In your example workbook, the income tax was 0% so I just went and inputted 10% to see it flow through, and nothing changed. Can I check, when I update the inputs in the table, should they auto flow through or do I need to do something? I am not familiar working with tables hence why I am asking? Also, will this Table work if I delete a row or insert a row (to remove or add new employees)?

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Help needed to create a pivot table..

    As to the income tax try pasting the following into cell D28 and then copying over and down :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As to deleting and inserting table rows. It appears that will cause problems with the values in column Q of the 2) Staff Costs Pivot sheet and their dependents (i.e. delete the row for A.N Other Counsel 1).
    If those problems can be overcome then it should be possible to insert and delete rows on the 2) Staff Costs Pivot and have the formula on the Ideal Output work correctly.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    14

    Re: Help needed to create a pivot table..

    thanks. That solved the income tax and it is great to know I can insert and delete rows.

    Can I ask assistance with another problem?

    I want to do the formula for the Annual Bonus payment (Bonus is calculated as Bonus % of Annual salary). I want to say "IF the current month equals Bonus Month" THEN do the sum of product... but I cannot get that bit around looking up the Bonus Month to work. I tried this formula but it is not working:

    =SUMPRODUCT((Position=$A47),MONTH(D$9)=MONTH(Bonus_Month),(SALARY*BONUS))

    I have now DEFINED the arrays for columns like Salary and Bonus and Bonus Month.

    The Bonus month in the table is a written month December. As I want the model to work over multi years, I cannot have an actual date format eg 12/2021 in that cell or else it will not work. So i need a formula to say "the month of december every year THEN look up and pay a bonus as per the Bonus Column etc.".

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Help needed to create a pivot table..

    Since the bonus month will always be December I would use: =IF(MONTH(D$9)=12,SUMPRODUCT((Position=$A47)*(SALARY)*(BONUS)),"")
    If that doesn't work please upload a sample of the file with the defined names applied.
    Let us know if you have any questions.

  9. #9
    Registered User
    Join Date
    01-15-2021
    Location
    London
    MS-Off Ver
    Version 16.44 2020
    Posts
    14

    Re: Help needed to create a pivot table..

    Quote Originally Posted by JeteMc View Post
    Since the bonus month will always be December I would use: =IF(MONTH(D$9)=12,SUMPRODUCT((Position=$A47)*(SALARY)*(BONUS)),"")
    If that doesn't work please upload a sample of the file with the defined names applied.
    Let us know if you have any questions.
    That worked.Thank you!.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: Help needed to create a pivot table..

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] VBA create pivot table in excel 2010 defaulted to 2003 pivot table format
    By lynnsong986 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2019, 02:05 PM
  2. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  3. Replies: 2
    Last Post: 06-16-2016, 10:59 AM
  4. Help with Pivot Table needed
    By Ohio Tournaments in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-21-2014, 07:03 AM
  5. Macro Help Needed - Link Pivot Table Filter to Regular Table AutoFilter
    By ycx1129 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-11-2011, 12:54 PM
  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. [SOLVED] VBA Code to create PIVOT from access needed
    By ST in forum Excel General
    Replies: 0
    Last Post: 07-26-2006, 10:25 AM

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