0

I'm trying to change a matrix table into a more straightforward database to allow multiple workbooks to be consolidated. I have seen a few potential options for this on here, but none seem to quite match what I want, and I can't convert to a Table as the headers are a formula.

The file is a template, and therefore needs to be able to vary to use on different projects, hence the varying headers. The existing table will look similar to this:

Client       Project             Phase  Staff   Mar-21  Apr-21  May-21  Jun-21
Problem Inc  Problem Project     1      Matt    2           
Problem Inc  Problem Project     1      Steve           3       
Problem Inc  Problem Project     1      Emma    1       1       
Problem Inc  Problem Project     2      Matt    4           
Problem Inc  Problem Project     3      Emma            1       2   
Problem Inc  Problem Project     3      Jane            1       3   
Problem Inc  Problem Project     4      Steve                   1        4
Problem Inc  Problem Project     4      Emma                    3        2

The first two columns will be constant for each file, so they are pre-populated each time the template is used. The months at the top would run for up to 2yrs, but will vary on start date (set on a different tab), so the headers are set based on the first month of the project for the first column, and then adding a month on each time. These needs to be able to change, so it can't be made into a static header and converted to a table. The phases, names and days against each month will be entered and modified over time.

There will be one of these for each project, each in different workbook, but all with potentially different starting months and durations (number of columns). To be able to combine them easily in to a single summary workbook I would like to have a summary table that is more consistent along the lines of:

Client       Project            Phase   Staff   Month   Days
Problem Inc  Problem Project    1       Matt    Mar-21  2
Problem Inc  Problem Project    1       Matt    Apr-21  
Problem Inc  Problem Project    1       Matt    May-21  
Problem Inc  Problem Project    1       Matt    Jun-21  
Problem Inc  Problem Project    1       Steve   Mar-21  
Problem Inc  Problem Project    1       Steve   Apr-21  3
Problem Inc  Problem Project    1       Steve   May-21  
Problem Inc  Problem Project    1       Steve   Jun-21  
Problem Inc  Problem Project    1       Emma    Mar-21  1
Problem Inc  Problem Project    1       Emma    Apr-21  
Problem Inc  Problem Project    1       Emma    May-21  
Problem Inc  Problem Project    1       Emma    Jun-21

So essentially it extends each phase to have a row for each member of staff, and each member of staff for each month, the adds the total days.

If possible I would like to avoid VBA, but think that's probably impossible. I don't see that there is a way to maintain fixed headers and still have the function of the table I need, but I equally want to be able to look at everything summarised from all the different individual project workbooks, so I think the simplified table is needed.

Is there anyway to do this or am I going down a deadend?

Thanks

user2846818
  • 1
  • 2
  • You could set up the Unpivot operation using Power Query. But when you import the "table", just choose the option "my table does NOT have headers". That way you can preserve the formulas. When you process the query for the report, you promote the first row to be headers -- They don't have to be formulas for the summary report, do they? – Ron Rosenfeld Mar 03 '21 at 23:31
  • That's so simple I want to go and bang my head against a wall! I was trying to make this far too complicated!! Thanks, that looks like it works perfectly! – user2846818 Mar 04 '21 at 10:33

0 Answers0