Excel-updating rows

QuestionEdit

QUESTION: I am tasked with keeping track of the travel of the various directorates in my organization. I have a main 'Data Entry' worksheet which I input all of the travel into followed by a worksheet for each individual directorates' travel so I can email the head of each directorate a spreadsheet containing only his directorates travel along with totals, percentages, etc.. One of the columns of the Data Entry worksheet is a drop-down where I can select the correct directorate for that trip.

Ideally, what I would like to be able to do is input or update a row on the Data Entry worksheet and have that row automatically upate or add to the corresponding Directorate Tab. Without having to copy and paste them all or update two seperate worksheets everytime I change something on the Data Entry worksheet.

I guess what I'm asking you is what you think the best strategy for managing this data would be.

I can see how this would be much easier to manage in Access, but my supervisor insists that I use2007.

ANSWER: Darrell

You could certainly write some VBA code to populate different worksheets depending on the data entered in the master worksheet.

However, rather than writing code to keep duplicate copies of the data, I would recommend that you simply enter your data in one place and use a Pivot Table or filters to display the data of interest. One of the things you do with a Pivot Table is create a sheet for each directorate. You could do that as part of an automatic e-mailing process. I believe you will find that a superior solution.

---------- FOLLOW-UP ----------

QUESTION: I messed with the PivotTable thing and didn't understand how to layout the tables the right way. It looks like pivot tables are to group and summarize data. All I want to do is create an identicle table to the one on worksheet 'data entry' on each directorates tab and filter the data for that directorate. All the while only having to update the 'data entry' worksheet. If this is possible through Pivot Tables, I would appreciate it if you could give me some more direction.

AnswerEdit

Darrell

That is exactly what Pivot Tables do. Writing code to manipulate other worksheets based on data entered in a master table will involve duplicating pivot table functionality. It took me a lot of years before I started using pivot tables (they have been around sinced 1995) but once I started I kicked myself every day for months for not using them sooner.