-1

I'm facing the following problem. I have a very large excel sheet in the format attached as a picture. For reasons beyond my control the basic formatting of this sheet needs to be maintained as people with no skill want to be able to edit this.

However, I need to work with the content and as it is quite large, it is really unreadable. So what I wanted to do is to be able to find the tasks belonging to different departments and the different people. My approach was to create two pivot tables where I want a dropdown list with either the department or the person to be able to filter on the tasks that that belong to each of them. I have found how to filter on a dropdown menu, however this is for column entries. What I want to do is to filter on the column labels and then display the tasks for which the column I selected is non empty.

I need some help on the direction in which I need to search for the answer to this problem as I'm currently lost in solutions that have nothing to do with this problem.

Thanks in advance

enter image description here

jeff
  • 151
  • 7
  • 1
    Just unpivot the above data using the following explanation: https://wessexbi.wordpress.com/2014/02/27/unpivot-nested-headings-with-power-query/ Afterwards, you can use the raw data whichever way you want to (including your suggested pivot table reports). Here is a somewhat related / similar solution I posted not too long ago: https://stackoverflow.com/questions/47283811/combining-different-fees-columns-to-create-pivottable-income-statement/47286415#47286415 Still, let me know if you have any questions. – Ralph Feb 14 '18 at 17:05

1 Answers1

2

You can use Power Query Excel 2010+ you can download and activate easily or by default in 2016 version. There you can keep this format for your users and a pivot table for control.

virtualdvid
  • 2,323
  • 3
  • 14
  • 32