Forum Discussion

vins322021's avatar
vins322021
Copper Contributor
Feb 24, 2021
Solved

weekly status report

Dear All, On a weekly basis I create a weekly status report with all the daily activities completed at work. Every microsoft excel tab corrisponds to a week, ie: CW01, CW02, CE03, etc. Now I am aske...
Macros and VBA
  • peteryac60's avatar
    peteryac60
    Mar 25, 2021

    vins322021 

     

    Hi Vin

     

    You can actually use my pivot table but if you want to follow the steps to create your own you need to do the following:

     

    • Select any cell in your pivot table
    • This will display the PIVOT TABLE TOOLS at the top right hand of the ribbon
    • Select the DESIGN option
    • On the far left select the Report Layout drop down and then choose Show in Tabular Format
    • Also on far left sect the Subtotals drop down and then choose Do Not Show Subtotals
    • The Sum of Effort in Hours columns defaults to decimal; you need to change the format to HH:MM; Place cursor on heading cell, right click mouse and select Value Field Setting; This opens a new window so select Number Format , then choose Custom; you can scroll down the values until you find hh:mm or you can type hh:mm if you can't find it in your list.

    I hope you can follow these steps!

     

    regards,

     

    Peter

     

peteryac60's avatar
peteryac60
Iron Contributor
Feb 24, 2021

vins322021 

Please provide a sample of your current format and I can look at this for you.

 

regards,

 

Peter

  • peteryac60's avatar
    peteryac60
    Iron Contributor
    Mar 25, 2021

    vins322021 

    Hi 

     

    I think what was being suggested was something like the attached. 

    The basic idea is to store all the data on a single sheet (ALL DATA) and then use a pivot table to select the actual week you need. I have done an example with the first 4 weeks. 

    On the SELECT WEEKLY DATA sheet, you select the week number at the top and the pivot will get the data that you need from the ALL DATA sheet.

     

    Hope this helps?

     

    Peter

    • vins322021's avatar
      vins322021
      Copper Contributor
      Mar 25, 2021

      peteryac60 

       

      Hello Peter,
      Thanks for your reply. I have tried to replicate your work but i could not quite make the same adjustments. Not sure what I am doing incorrectly.
      Thanks again for your time and your patience.
      Dincerely
      Vin

      • peteryac60's avatar
        peteryac60
        Iron Contributor
        Mar 25, 2021

        vins322021 

         

        Hi Vin

         

        You can actually use my pivot table but if you want to follow the steps to create your own you need to do the following:

         

        • Select any cell in your pivot table
        • This will display the PIVOT TABLE TOOLS at the top right hand of the ribbon
        • Select the DESIGN option
        • On the far left select the Report Layout drop down and then choose Show in Tabular Format
        • Also on far left sect the Subtotals drop down and then choose Do Not Show Subtotals
        • The Sum of Effort in Hours columns defaults to decimal; you need to change the format to HH:MM; Place cursor on heading cell, right click mouse and select Value Field Setting; This opens a new window so select Number Format , then choose Custom; you can scroll down the values until you find hh:mm or you can type hh:mm if you can't find it in your list.

        I hope you can follow these steps!

         

        regards,

         

        Peter

         

    • vins322021's avatar
      vins322021
      Copper Contributor
      Mar 25, 2021
      Hello Peter,

      Thanks for your assistance. I have tried to replicate your work but could not quite do it correctly. Enclosed you will find the same file, reviewed. What am I doing wrong now?

      Thanks again for your patience and for your time.

Resources