• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract data from detailed WBS to create simplified report, while retaining WBS format

Hello!

Attached is a file titled: Test WBS. The first tab, 2019 Projects, shows a very detailed list of projects in WBS format. The challenge I have is reporting projects back to our VP, who does not need, or want, to see every detail.

In the 2019 Projects sheet, I've indicated in Column D (VP View) those items I need the VP to see; marked with 'X's'. I've created a second tab, VP View. My goal is to have all of the rows with 'X's' from 2019 Projects show up on VP View (minus the gantt chart). I would like to retain the WBS hierarchy in the VP View, so it would like something like this...

1 Project 1
1.2.2 Email - Deploy
1.4.2 Live Webinar - Internal ONLY
1 Project 2
2.2.2 Email Save-The-Date - Deploy
2.2.3 Email with download instructions - Deploy
1.4.2 Live Webinar
3 Project 3
3.2 Assessment Worksheets to SD's
3.3 Assessment Worksheets back from SD's
3.4 MBR to SD's
3.5 MBR back from SD's
3.6 Monthly Totem Poles
3.6.1 Product 1
3.6.2 Product 2
3.6.3 Product 3
3.7 Monthly Department Report
3.7.1 Monthly Report to SOP
3.7.2 Monthly Report to SD's
3.7.3 Monthly Report to All
4 Project 4
4.2.2 Draft 1: to Team
4.3.4 Final Draft: to Print

Thank you in advance for your consideration!
 

Attachments

  • Test WBS.xlsx
    69.4 KB · Views: 7
Wow! This is exactly what I need! Pivot Tables are a weakness for me, so I greatly appreciate your help. I was afraid this was going to be more complex. Nice and easy! Thanks again!
 
I celebrated too soon. The data is not sorting in the proper WBS order. I tried formatting the numbers to text, no luck. I added a 'sort' column, which basically numbers each row 1 - 168 (which is the total number of rows I have today). However, when I sort on that column, no luck the numbers are still out of whack.


61550
 
Please ignore this request. For reasons I can't explain, I was able to get the 'Sort' column to work so the WBS integers and decimals are in the correct order. Thank you for your consideration!
 
I'm not sure if I should start a new post, but have another request related to this post. See attached, pivot table worksheet, column N. Is there a way to auto-indent a pivot table based on the number of decimal points in an integer. For example 4.4, indent = 1 because there's only 1 decimal. If the integer is 2.3.5, then you would indent = 2 and so on. I would like to achieve this without using VBA, if at all possible.
 

Attachments

  • Test WBS.xlsx
    73.5 KB · Views: 2
Yodelayheewho
Seems that You skipped my reply ..
Your left 'something' are not numbers!
Indenting ... should be possible
... but still there would be Your 'sorting challenge'.

Hmm?
You could filter eg manually Your '2019 Project'-sheet with 'VP view'-column
... there are already those indents done!
>> then no need any VBA.
 

Attachments

  • Test WBS (1).xlsx
    75 KB · Views: 7
Embarrassed...sometimes I overthink things...LOL.
Yes, I can filter on '2019 Project' sheet. So, for the VP report, I'm doing that. Yeah!
My next challenge is to create a dashboard. I've done this before, just need to dust off the formulas.

Thank you!
 
Back
Top