Excel help!

[DDY]

Need Excel help! Here's the situation... I've got some document folders which will be sent away for archiving in boxes, I've made a spreadsheet listing all the folders and which numbered box they're in.

What I want to do is generate a sheet for each box which lists its contents. How can I achieve this in Excel?

Thanks

[Willzzz]

One way to do it would be to create a pivot table (Data -> Pivot Table) of the data and then arrange that by box number, and then double click the pivot table for each box number to create a new sheet for that data.

How many boxes are there?

[DDY]

I have about 20 boxes.

[scaryjim]

Use Access

If you can't get Access, then maybe pivot tables, as Willzzz suggests, will do the job. Been a long time since I crowned myself God of Excel though, so if you're stuck on Excel I'm not going to be much more use.

But seriously, if you can get Access then your life will be *much* easier.

[KeyboardDemon]

Here's a tutorial on Pivot tables.

An alternative might be to take your Excel spreadsheet and import it into Access (if you have it) and then set it up as part of a relational data base, but if it were me and I was doing it in Excel, I would have just created a single table and used filters or grouping to get specific data relating to individual boxes. So I'd create the table with:

Box number the folder is stored in, folder number and then folder content details (this might be one field or several fields depending on how much data you need to store about each item).

Then when I want to find a specific box contents I can use the filter option to bring up only that boxes details, or I can filter by folder, or just search everything in one go to find a specific document to see that box and which folder in that box to look in.

[Willzzz]

Yeah, what is the purpose in these lists?

Are they to be printed? If so I'd just use a filter and then you can quickly print 20 lists.

Or you could write a macro, but if you don't know how to do that it is tricky to explain.

[DDY]

Thanks everyone.

I forgot to mention, the main reason I want the contents of each box generated is so that I can print and label the boxes easily. I'll give pivot tables a go and see how far I get with it.

Also, there's no way I'm using Access for two reasons, first, despite learning how to use Access way back when I did GCSE IT, I have absoloutely no idea where to start now that I've opened up Access for the first time since. Second, it's likely that someone else with "common office" IT skills, like mine, would need to edit or add to the list, using Excel would mean less time explaining how the thing works.

[DDY]

Success! Filters alone worked, but I preferred the 'stacked' layout of a compact pivot table because it fits perfectly on my labels.

Now I'm attempting to write a macro to print all the box labels D:

Failing that I'll just hit print twenty odd times...

Thanks again!

EDIT: Damn that easy, just written a macro which prints the box labels, I'm totally stoked. This is the first time I've practically used my MATLAB training, albeit a single for loop

[KeyboardDemon]

Cool. Glad you got it sorted.