Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

copying data from one excel sheet to another

May 26, 2005 11:14AM PDT

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

Discussion is locked

- Collapse -
Not really possible in Excel.
May 27, 2005 8:29PM PDT

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

- Collapse -
Thanks kees
May 28, 2005 6:40AM PDT

the main problem was that the company doesn't want to spend any money on any new programs and the operators seems very reluctant for any changes.

but i managed to convince them and did it in access as the #$%$ company doesn't want to spend on VB or anything.

it took an hour and a half, the new system is in place with MS Access and looks pretty good also.

thanks mate for ur support. sorry for the trouble..

sharan