Designing the best structure for data sheet

[Kenny Bones]

I've been wondering for a few hours now and I'm completely stuck. So I thought I'd throw you guys a nut here

Kinda hard to explain, but I'm trying to set up a Workbook to sum up work clothes for each employee in our company.

The sheet looks like this:
http://img80.imageshack.us/img80/3286/capturenxf.png

Now, what I want to do is have one sheet that acts as a master data sheet for each employee. And I'd like to remove the "Paul Mccartney" in the example above with a combobox or something, listing each names from the data sheet.

Something like this:
http://img51.imageshack.us/img51/5512/capturedko.png

But, the problem as I see it is that there are columns for each item for summer and winter periods. And how can I structure this?

[royUK]

probably the best way would be a Pivottable, but you could post a dummy workbook instead of images

[teylyn]

Please.

This is an Excel forum, not a Photoshop forum. If you want help with spreadsheets, post spreadsheets. Nobody feels like re-typing your data to test a suggestion.

[Kenny Bones]

Sorry..
Here's the workbook. Although I don't see why you need it, it doesn't really contain much functionality. Just my basic thoughts so far.
http://www.2shared.com/file/3yfM7yJo...esExample.html

Edit: In the second sheet, I thought I'd sum it all up as well. So when I select "summary" from the combobox in the first sheet, a summary of all names appear.

[teylyn]

Please use the forum facilities to upload files, rather than untrusted external file sharing sites.

[Kenny Bones]

Aha, I was looking for something like that. Here

[teylyn]

Hello,

there are a few things to sort out here.

In your summary sheet, you distinguish between summer and winter. The data source in the Names sheet has no indicator for this distinction. How do you want to determine if an order is for summer or winter apparel?

The best practice way to do this is to set up a data entry table with the columns

Name | Art.Nr | Item | Price | Season | number

Enter the data for each transaction on its own line, i.e.

Paul | 12345 | Jacket | 49 | Winter | 2

Then you can build a pivot table to summarize the data if you use a page filter for the person,

cheers,

[Kenny Bones]

Yes, but the problem is that each item (Jacket etc) needs to be registered by seasons as well. "Paul" might have one Jacket for Summer and two for Winter. And there lies the problem as I see it.

[royUK]

There's no way you can split summer/winter to the summary unless there is some way to distinguish in the data sheet

[teylyn]

"Paul" might have one Jacket for Summer and two for Winter. And there lies the problem as I see it.

No problem there at all. You need two rows of data for Paul, one for summer, one for winter.

Name | Art.Nr | Item | Price | Season | number
Paul | 12345 | Jacket | 49 | Winter | 2
Paul | 12345 | Jacket | 49 | Summer | 1

[Kenny Bones]

Right, that's gotta be the only way of doing this. But, that means that I have to somehow merge those two rows, displaying them as one in the summary sheet. Is it possible to do that using a pivot table? Or would I need to use a macro? I think I'll be able to do this myself using a macro, but I'd rather it work in real time than having to click a button firing a macro.

Edit: Just found that Pivot tables do that by default. Opening a new thread on how to customize this table to my likings.