Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula to populate names in a Report

    Hi Excel Experts,

    I extract data on a monthly basis and prepare report. The data sheet has the country(col A), name of the agent (Col B) and order date(Col C). I have used the networdays formula to show the number of days and the if condition to show if the order is red, amber or green.

    I have created a report for the same which is country and the agents and the number of red, amber and green cases against each agent. I was able to create this using countif formula. I have created this report template using pivot table.

    Each month there will agent who will get added and deleted in the data sheet. Is it possible to have a macro or a formula that will pick up the agents from the data sheet automatically (Col B)? Instead of me creating a pivot each time and checking for additions and deletions and updating the reports.

    Looking forward to a simple but great solution. I have attached the excel sheet for your reference.

    Thanks
    Baiju
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Why not create the pivot table one time, format as desired and then refresh it when the source changes. It should be updated automatically with the refresh...

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes steve that is what i intend to do. I was just wondering if this could be done through formulas or macro.

    Thanks
    Baiju

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You should not need code to create the pivot since it seems you only need to do it one time. But if you want to see the essential code for creating a pivot table, by creating the pivot table with the macro recorder. The macro recroder will also show you the code to refresh the table.

    Steve
    PS: A formulaic approach will not work very well since as you indicate the type of data in the fields will change. The pivot will update with a refresh.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •