I really would advise you to do this in MS Access (if you want to stick to MS Office). You enter the data once, and can make several reports (sorted or summed on date or on sales executive or on customer or on week or on month or whatever you like).
To do what you want in Excel the easiest thing to do seems to me to only enter the data once, then write a VBA macro that does the following things:
- erase all info all sales persons sheets
- loop through all sales of all dates and them to the sheet of the corresponding sales person
So essentially you automatically rebuild the whole reporting structure from the base data when you execute this macro.
It's a few pages of VBA, and depending on your experience in the language and in programming in general it will take you anything from 6 hours to 6 weeks to write it.
Kees
Basically this is for maintaing sales report.
i am maintaining 2 excel sheets 1. daily sales report and 2. Individual monthly report.
Daily report will have the sales made by each sales executive on a given date(amount, kind of product and their postcode). This workbook will have sheets for each date in a month.
monthly report has sheets for each person, so if one person makes a sale say on the 24/05/2005, it will be updated against that date on the monthly report for that particular person. like wise for each executive.
Now the problem is i have to enter in both the sheets manually, ie, on the daily report aswell as on the monthly report. when the sales made are too high and the executives are also high it make the job frustrating.
is there any easy way of updating only in one place?
fields in both the sheets :
daily report
Agents Name Customers Name Policy No Quote No Post Code Type of Policy Amount.
monthly report
Date Customers Name Policy Number Quote No Post code Type of Policy Amount.
when i enter on the daily report it should take the date from the sheet name and put the details into the monthly report aswell.
i understand that it will be easy in either vb or access or anyother tool but is this possible in excel

Chowhound
Comic Vine
GameFAQs
GameSpot
Giant Bomb
TechRepublic