Dynamic way for senior management all the way to the sales person to view their data

[tryingtoexcelatexcel]

Hi All,

I'm trying to figure out a way for senior management down to the sales person to be able to view their stats on a weekly basis.

I was thinking of having the data section which will be a table and then each person can just copy and paste the data they retrieve into the data tab. For example if they wanted the next 3 months it would be in the same format as the next 1 year.

Then I was thinking of using a pivot table (attached sample of 1 office only) so it could show on a macro level, country performance down to sales person performance.

My question is, is a pivot table the best way to present this data? I'm still trying to visualize how to present this data so that all the user needs to do is input the data into the data tab and then can use drop down lists to narrow down their selection.

If at the country level then for example see sales summaries at the country level, then when choosing specific countries they can choose specific regions, then specific offices then specific managers and finally specific sales persons and the data shown will change accordingly.

Is it better to create multiple pivot tables or is there a better way to go about this?

Thanks in advance.

[Richard Buttrey]

A Pivot Table is likely to be the most efficient and useful way of handling your data.

However your current sheet called 'Pivot Table' isn't a PT. The data on which the PT relies needs to be in a normalised two dimensional table with column fields for unique types of information and Rows for a particular record or piece of data. Hence you should create a data table with the following fields. I've assumed the fields on the sheet you've called PT are the important ones, but if there are others then add them. Be clear though a Field name is an identifier or a unique TYPE of information, not for a particular subset of a Type of information. So for instance a Date is a Type of information but additional columns for months wouldn't since the month is determined by the date.

Other fields like Tour Name could be lookup fields from a subsidiary table of Tour Codes & Names.


Date
Region
Team Manager
Sales PIC
Velocity
Client Code
Tour Ref
Adults
Gross Sales in EUR (MSA)

When you've put together a typical table of values then upload the workbook and explain the sorts of reports you want to get from it. Usually it's not necessary to have several different PTs since the whole point of a PT is that it is changeable in seconds just by dragging and dropping field names around and by usinf Slicers to filter the PT.

[tryingtoexcelatexcel]

Hi Richard, I have included a copy of the pivot table I created.

Is there anyway to lock the Sales Office Report Filter? If I wanted Australia and Beijing to only be able to access their respective tables is there a way to lock this with a password. So for example I would issue the team in Australia a password and the team in Beijing a password.

Would there be another way to create this form besides using a pivot table or would a pivot table be best to use here.