Feb 24 2021 07:09 AM
Feb 24 2021 07:09 AM
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?
Feb 24 2021 09:19 AM
Feb 24 2021 09:57 AM
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!
Feb 24 2021 10:01 AM - edited Feb 24 2021 10:17 AM
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.
Feb 24 2021 11:05 AM
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.
Mar 25 2021 03:23 AM
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?
Mar 25 2021 05:34 AM
Mar 25 2021 08:52 AMSolution
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:
I hope you can follow these steps!
Mar 26 2021 01:13 AM