Hello,

Just sharing my view, I would stick with the source data format and use a Pivot Table to arrange the data as required.

Br,
Anders

Hi Bax,

I agree with Anders. Your data is currently in the correct tabular format. From here you can get your summary with a PivotTable. See attached.

Mynda

Hi Anders & Mynda,

Thanks for the input. I was hoping to limit the number of columns showing the consultant information as in the full data table there are hundreds of consultants and doing it the way you suggest would result in a large pivot.

There will generally only be two or three consultants per time sheet and therefore I was hoping I could have a generic column heading like "Consultant 1", "Consultant 2" etc. I will be using the resultant table to merge with another query that shows the sales revenue by time sheet and I want to add the consultant splits to this.

Regards

Bax

Hi,

Well, you can always create one PivotTable, set it up as you want showing only the consultants you need for the specific time sheet. When done, just copy the worksheet containing the PivotTable to a new worksheet, in this copy you change the names of the consultants and so forth. It means you will have lots of worksheets, but these don't have to be in the same workbook if you don't want to.

Or, you can create one big PivotTable and then in another worksheet you create the time sheet as you want to present it and use GETPIVOTDATA() function to retrieve the information from the PivotTable.

You can also use the DGET() function to get information direct from the source data, no need to create a PivotTable. It all depends on how you want to present the data.

But I would still recommend you to stick with the format in the source data.

Br,
Anders

Hi Bax,

You could add a helper column to your source data that numbers the consultants in each time sheet. You can then use this field in your column labels.

Whatever you do, don't change the original data layout to anything but Tabular 🙂

Mynda