+ Reply to Thread
Results 1 to 9 of 9

Need consolidated pivot or other pivot to extract data

  1. #1
    Registered User
    Join Date
    07-15-2012
    Location
    NW Ontario
    MS-Off Ver
    Excel 2010
    Posts
    19

    Need consolidated pivot or other pivot to extract data

    A new requirement from my invoicing company has me going in circles. I have a single sheet containing data that I input for each call. The requirement is to atomically populate the invoice sheet based on some specific criteria. I need a populated invoice produced each week (Sat to Friday,, ie Feb 8 to 14th,,, Feb 15 to 21 ect) The weekly invoice can only be populated if the call is indicated as closed in the data sheet. Once the call is closed I need to total all the hours for that call and populate that on a single line entry with the last date worked on. If the data indicates that the call is a project, and the call is closed, that information is populated to the data section. Once the weekly sheet is created, I need a new sheet created and the template ready for the next week to be again automatically populated.

    In my old system I was using weekly sheets and consolidated pivot table. I can have that do part of what I need It won't do the job. I prefer to start a fresh year with a single data source. I'm not even sure how to approach my problem, any help would be much appreciated. Thanks
    Attached Files Attached Files
    Last edited by Lowtech; 02-09-2014 at 12:00 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help on approach to problem

    Hi, unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    07-15-2012
    Location
    NW Ontario
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Need consolidated pivot or other pivot to extract data

    I don't know if I'm locked out for reply's, or no one has any input. I didn't include my effort on a pivot table because it does not work. Mr. Mod if I'm locked please advise and I will delete. Thanks

  4. #4
    Registered User
    Join Date
    07-15-2012
    Location
    NW Ontario
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Need consolidated pivot or other pivot to extract data

    I have uploaded my new effort to solve my issue. I'm in over my head, could someone comment if its possible to have text show in a pivot. If I could see the text there I may be able to move forward with my problem.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need consolidated pivot or other pivot to extract data

    Hi,

    Thanks for changing the title.

    You'll need to manually add the results you expect to see on the Invoice sheet since it's not clear to me where you expect all the numbers to come from. When we can see what you expect, no doubt we can work out how to get there.

  6. #6
    Registered User
    Join Date
    07-15-2012
    Location
    NW Ontario
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Need consolidated pivot or other pivot to extract data

    Thank you for checking this out. I have entered data that I would expect in my two invoice tabs. At the end of each week, I would look at my pivot table and populate the invoice, but ONLY if the call is shown as complete (yes, in the completed column) If the call showed complete AND the project column was "yes" then that call information would populate to the projects area of the invoice. The invoice can only have one line for each call-id and that call-id can not be duplicated in any other invoice.

    Since I have to submit weekly invoices, and since the invoice can not contain an open call, this makes my problem difficult (for me). My pivot table will total each call-id correctly but I need it to only do that if the call is closed (yes in Complete column) Hope I'm making sense, I'm going around in circles.





    Helpme.xlsx

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need consolidated pivot or other pivot to extract data

    Wouldn't it be simpler to use a Data Advanced Filter to first extract a unique list of Call Refs straight from the Data sheet to column E on the Invoice sheet provided they met the criteria of being closed, and then use SUMIFS functions in the invoice to summarise the various totals?

    I'm a great believer in PTs but this seems to me one instance when it's an unnecessary step since you're still having to refer to the PT to get your data. The Data Filter could be a simple two line macro that you could run at the click of a button.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Need consolidated pivot or other pivot to extract data

    Wouldn't it be simpler to use a Data Advanced Filter to first extract a unique list of Call Refs straight from the Data sheet to column E on the Invoice sheet provided they met the criteria of being closed, and then use SUMIFS functions in the invoice to summarise the various totals?

    I'm a great believer in PTs but this seems to me one instance when it's an unnecessary step since you're still having to refer to the PT to get your data. The Data Filter could be a simple two line macro that you could run at the click of a buttom.

  9. #9
    Registered User
    Join Date
    07-15-2012
    Location
    NW Ontario
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Need consolidated pivot or other pivot to extract data

    Thank you, my real issue is finding a good method of attacking this. My method is not working for me and I really appreciate the suggestion. I use PT a fair bit and I agree in this instance it is cumbersome. I like your suggestion and I will work on it. Thank you

+ 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] Help for v-lookup approach instead of manually cell linking approach - Excel 2010
    By Mirisage in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-31-2013, 01:32 PM
  2. Complex Macro/Problem Approach
    By lmorrison in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-01-2011, 01:29 PM
  3. how to approach this?
    By iori in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-29-2006, 12:03 PM
  4. How to approach this?
    By mevetts in forum Excel General
    Replies: 1
    Last Post: 01-10-2006, 12:25 PM
  5. [SOLVED] New approach
    By davegb in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-06-2005, 12:35 PM

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