Excel 2003: How to create new automatically updating worksheets using data in an existing sheet

[Lizochka]

Hi everyone,

My knowledge of Excel is somewhat limited, so I apologize if I am
asking an overly simple question here.

I am using Excel 2003, and what I'm trying to do is organize a table
of courses. The master table currently exists on a single
spreadsheet, with columns such as Professor Name, Course Number,
Course Title, and Semester. What I have been asked to do is create
another sheet within the the same workbook that organizes the data by
Professor Name. Basically, I need to create a new sheet for each
professor (30 in total) that would look at the data in the master
table and tell me that Professor Xavier is teaching Mutants 101 in the
Fall and Mutants 102 in the Spring. The problem is that my boss would
like this to happen automatically, so that if Professor Xavier is
teaching a new course this year that gets added to the master, it
would automatically also appear on his individual spreadsheet.

How do you suggest I go about this? I thought of pivot tables, but I
didn't see a way to separate my data by Professor Name into different
tables. Any suggestions would be much appreciated, and I apologize
for my ignorance if the answer is right in front of my nose.

Regards,
Liz

 

[Dave Peterson]

These are not automatic in the sense that either react as soon as you change/add
a record, but each will automate what you want.

Personally, I'd want to use one sheet to do data input, then create the new
sheets whenever I wanted.

=========


You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb