Automatically generating lists/tables based on information within the spreadsheet

**Lord451** · 01-06-2012, 03:03 PM

I'm trying to figure out a way to create an automatically updating table based on information that's already present.

What I'm working on is a class list for inmates within a county jail (which is why I don't want to post most of what I have, it's generally considered privileged information). I've got them arranged on a worksheet titled by the pod they're housed in with the person's name in column A, and further colums indicating classes they're in and a little bit of information. Is there any way for it to look at what classes they're in and automatically generate a class list? The list needs to have their name, a space for a signature, and a number that's assigned to them, and be able to cut and paste the person easily and update the new list they're put into.

If I need to edit some of the information so I can post a censored list to explain what I'm needing I can. And because I work for the county, I don't have the option of using any addons to improve it. I have base Excel 2007.

If I can figure out how to make an auto-updating table like that, I should be able to figure out the formatting to make it pretty. The reason I need it to be automatically updated is the frequency that these class lists change; it's pretty much daily.

Any help would be immensely appreciated.

**dangelor** · 01-06-2012, 05:56 PM

Without seeing your data, I would suggest using a pivot table or advanced filter/copy.f

HTH,
Rich

**Lord451** · 01-09-2012, 02:31 PM

Ok, here's a selection of the data with the names removed. It's not accurate as far as the number go, but basically I need something that will generate a class list with the names on the left (Column A), and booking number on the right (Column B) based on whether they're in a class or not.

I like a lot of what you can do with the pivot table, but what I really need is something that will automatically generate this data every time it changes. If there's a way to filter the data in the pivot table I'm not sure what it is yet. In a day it's not uncommon to have 20 to 30 changes, every single day so I need something that's relatively automatic as far as updating goes.

What I'd like is if you write "class" in that table, it automatically puts them on a list formatted more or less like the example at the bottom; if there's a way to have it make space and move things underneath it so that the size limits don't become a problem that'd also be a huge plus.

**dangelor** · 01-09-2012, 05:33 PM

Here's your data with a PT. Swap the class name in/out of the report filter.

HTH,
Rich

**Lord451** · 01-09-2012, 06:05 PM

Wow, excellent. That's extremely close to what I need.

A few last questions, is there a way to remove the titles from the pivot tables? In particular the AA and Class filter that's written at the top so it's just the data, and can you think of a way to include the formatted dark box for a signature line? I've been playing with the options but can't figure a way to do either of those automatically.

Also, how did you get the booking # to display beside the name? When I do it, it places it under the name and I can't find the proper formatting box.

**dangelor** · 01-09-2012, 06:35 PM

To remove the labels, look under Pivot Table Options - In xl2010, it's under the Display tab. Not at my xl2007 system at the moment.

As for the signature, try creating an extra column named "Signature" and drop that field into Values...

**Lord451** · 01-09-2012, 06:42 PM

Ok, I'll give that a shot, but first how did you get the numbers to expand to the right instead of below the names? I've been trying to figure that out for awhile. I'm just not finding any formatting options for it.

**dangelor** · 01-09-2012, 07:01 PM

I believe it was just by removing the subtotals.

**Lord451** · 01-10-2012, 04:46 PM

Ok, I figured it out. With the pivot table selected, go to the top right section and it has a heading for Pivot Table Tools. Select Design, select Report Layout and then Show in Tabular Form. You might have had yours already set to default it like this.

Thanks, this works beautifully and once I finish setting it up will save a huge amount of time.

**dangelor** · 01-10-2012, 04:52 PM

Glad to be of some help. Enjoy the day.
-Rich

Automatically generating lists/tables based on information within the spreadsheet

LinkBack

Thread Tools

Rate This Thread

Display

Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Re: Automatically generating lists/tables based on information within the spreadsheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions