Question on layout best practice

[Adam88]

Afternoon,

Just after some advice on best practice for how to layout data to create reports from it.

I have a worksheet for weekly wage calculation. I have each row configured to store data on the date, name of employee, gross pay, net pay etc, however one of the columns relates to the different sites they have worked on for that period. At the minute, I have this column set up to list each additional site (after the first) to be entered into the next row down (as it could range from 1 site to as many as 7). I decided on this approach instead of adding 7 columns and potentially having 6 empty fields if the employee only works on 1 site that week.

The problem I face is that either way I do it, creating a pivot report on that data always displays incorrectly. With the way I have it now (as explained above) it only shows the first site the chosen employee has worked on and ignores the additional sites. Doing it the column way, displays the sites as expected but also (blank) results for the columns that have no data.

Can you suggest some methods or literature I could read that would help work out what I should be doing?
Thanks

 

[ajamess]

Not sure exactly of your formatting you're looking for or your goal of your pivot (Screenshots with anonymized data could be helpful). But my first guess would be in the pivot table put both the employee and the sites they worked on in the rows section, filter out the blanks. So you'd have each employee, then that employee broken into the subset of sites they worked on.

 

[Adam88]

Thanks for your response. I've given your suggestion a go and it doesn't quite work the way I was hoping. I may not need a pivot table to do what I need, I just basically want a sent of filters on my data that I can narrow the records down to a smaller selection. I've tried applying the standard filter to the data and that works pretty well although it still wont pick up the additional sites as they appear underneath the main row of data. I've added a link to a screen grab of the test worksheet below:

http://tinypic.com/r/e9t314/9

Hopefully you might see a solution to the issue!

Thanks

 

[ajamess]

I'm not sure exactly what banking ref is, but if important you might want to remove that from the picture.

What would you be filtering on? How many possible sites are there?

 

[Adam88]

Its nothing important, just admin related!

I'd be filtering predominantly on Sites Worked and Employee, of which there could be up to 7 sites for an employee on any given week. My logic is;

I want to be able to see how many employee hours in total have been spent on a particular site based on a selected week.

Thanks