SOLVED

weekly status report

%3CLINGO-SUB%20id%3D%22lingo-sub-2162642%22%20slang%3D%22en-US%22%3Eweekly%20status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2162642%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3CBR%20%2F%3EOn%20a%20weekly%20basis%20I%20create%20a%20weekly%20status%20report%20with%20all%20the%20daily%20activities%20completed%20at%20work.%20Every%20microsoft%20excel%20tab%20corrisponds%20to%20a%20week%2C%20ie%3A%20CW01%2C%20CW02%2C%20CE03%2C%20etc.%20Now%20I%20am%20asked%20to%20only%20use%20one%20sheet%20with%20a%20column%20for%20CW%20to%20filter...%20Could%20anyone%20please%20help%3F%3CBR%20%2F%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2162642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2162919%22%20slang%3D%22en-US%22%3ERe%3A%20weekly%20status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2162919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F977442%22%20target%3D%22_blank%22%3E%40vins322021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20provide%20a%20sample%20of%20your%20current%20format%20and%20I%20can%20look%20at%20this%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eregards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2163004%22%20slang%3D%22en-US%22%3ERe%3A%20weekly%20status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2163004%22%20slang%3D%22en-US%22%3EGo%20to%20each%20tab%2C%20go%20to%20the%20end%20of%20the%20data%20and%20find%20the%20last%20blank%20column%2C%20name%20the%20column%20reporting%20week%2C%20name%20it%20the%20same%20as%20the%20name%20of%20the%20tab%2C%20dump%20all%20the%20data%20into%20a%20new%20file%2C%20done.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2163111%22%20slang%3D%22en-US%22%3ERe%3A%20weekly%20status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2163111%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3Bthanks%20for%20sharing%20the%20info.%20As%20suggested%2C%20I%20have%20renamed%20the%20last%20blank%20column%20with%20the%20title%20of%20the%20reporting%20week%20tab%20(CW01%2C%20CW02%2C%20CW03%2C%20etc).%20What%20do%20you%20mean%20exactly%20with%3A%20%22%3CSPAN%3Edump%20all%20the%20data%20into%20a%20new%20file%22%3F%20Thanks%20again!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2163139%22%20slang%3D%22en-US%22%3ERe%3A%20weekly%20status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2163139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EThe%20current%20format%20is%20a%20simple%20spreadsheet%20with%20different%20tabs%20that%20will%20corrispond%20to%20the%20week%20number%20of%20the%20year%20(CW01%2C%20CW02%2C%20CW03%2C%20etc.).%20The%20only%20formula%20used%20is%20to%20sum%20up%20the%20total%20amount%20of%20weekly%20worked%20hours.%20Basically%2C%20I%20am%20asked%20to%20avoid%2052%20tabs%20(each%20for%20every%20week%20of%20the%20year)%20and%20only%20have%20a%20column%20with%20a%20dropdown%20option%20that%20selects%20the%20week%20number...%20Hope%20this%20additional%20information%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2163147%22%20slang%3D%22en-US%22%3ERe%3A%20weekly%20status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2163147%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F977442%22%20target%3D%22_blank%22%3E%40vins322021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20provide%20the%20file%20it'll%20be%20easier.%20I%20attached%20what%20I%20thought%20would%20be%20best.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreate%20a%20new%20tab%2C%20go%20to%20each%20tab%20where%20you've%20labeled%20per%20week%2C%20copy%20the%20entire%20data%20set%2C%20go%20to%20the%20new%20tab%20and%20copy%20it%20that%20to%20a%20brand%20new%20tab%2C%20repeat%20for%20all%20the%20tabs%20labeled%20so%20you%20have%20one%20tab%20with%20all%20weeks%20in%20it%2C%20click%20into%20the%20data%20and%20press%20Control%2BT%20to%20create%20a%20table%2C%20check%20the%20box%20that%20your%20tables%20has%20headers%2C%20and%20you're%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20much%20better%20if%20you%20complete%20this%20and%20then%20create%20a%20pivot%20table%20based%20on%20this%20table%20of%20data%20because%20you%20can%20visually%20see%20this%2C%20filter%2C%20and%20show%20the%20data%20in%20a%20more%20meaningful%20way%20than%20just%20in%20a%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 asked to only use one sheet with a column for CW to filter... Could anyone please help?
Thank you!

17 Replies

@vins322021 

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

 

regards,

 

Peter

Go to each tab, go to the end of the data and find the last blank column, name the column reporting week, name it the same as the name of the tab, dump all the data into a new file, done.
Spoiler
 

@Jpalaci1 thanks for sharing the info. As suggested, I have renamed the last blank column with the title of the reporting week tab (CW01, CW02, CW03, etc). What do you mean exactly with: "dump all the data into a new file"? Thanks again!

@peteryac60 
The current format is a simple spreadsheet with different tabs that will corrispond to the week number of the year (CW01, CW02, CW03, etc.). The only formula used is to sum up the total amount of weekly worked hours. Basically, I am asked to avoid 52 tabs (each for every week of the year) and only have a column with a dropdown option that selects the week number... Hope this additional information helps!

@vins322021 

 

If you provide the file it'll be easier. I attached what I thought would be best.

 

Create a new tab, go to each tab where you've labeled per week, copy the entire data set, go to the new tab and copy it that to a brand new tab, repeat for all the tabs labeled so you have one tab with all weeks in it, click into the data and press Control+T to create a table, check the box that your tables has headers, and you're done.

 

It would be much better if you complete this and then create a pivot table based on this table of data because you can visually see this, filter, and show the data in a more meaningful way than just in a table.

@Jpalaci1 
Dear Jpalaci, I attach the document. As suggested, it might be easier

@Jpalaci1 
Aweasome, thank you! while updating this spreadsheet I always have to create a tab of the current week. Correct?

@vins322021 

 

No, it'd be more effective to add to the new to the consolidated one and ditch the week tabs. Just make sure to label each week when added. If you do choose to manage then just have that week with the consolidated one and then add to consolidated and then delete that week's data.

@Jpalaci1 

Truly appreciate, thank you!

@peteryac60 

Please find attached the sample status report. According to the indication received, I should only have one sheet with a fileter to select every week. Not sure on what to do. Thank you.

@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

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.

@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

best response confirmed by vins322021 (Occasional Contributor)
Solution

@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

 

Hello Peter,
I am now able to mirror your example and replicate the pivot table.
Thanks for your kind assistance.
Regards,
Vins

@vins322021 

 

Hi Vins

 

You are welcome - glad to help.

 

kind regards,

 

Peter