Software

Ask something
Discussion Locked

Excel

0 helpful
5 replies

by dabsinpa

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

On a monthly basis, I use one standard excel spreadsheet to enter data from our sales personnel. I save the file with the batch number (this changes monthly because it represents the month and year)and sales persons name then continue reusing the standard spreadsheet. This results into approximately 200-250 individual spreadsheets which my Director takes the totals only from the printed sheet and enters that information into a new master spreadsheet representing total monthly activity.

I wanted to create something so my Director doesn't need to enter this data monthly. My first thought was to link the totals row from the standard spreadsheet into hers. However, renaming the file loses the link (right?). Then, I played with creatinga workbook with hundreds of worksheets that we could rename with the batch number and name. Again, I lose the link. Then, I read about the template wizard. I'm still working on that but am not sure if this is my best solution.

Any help you could give me would be greatly appreciated. By the way, I suggested Access to my Director but she hates Access and I'm not that familiar with it myself.

Thank you.

Dorothy

5 replies

by dklippert

You can set up a clean workbook as a template, merely by using File>Save As and change File Type to Template. You would then use File New to open a fresh copy.
First come up with a standard naming convention (April98.xls, 0498Sales.xls).
Lay out the work books in the same way.
On each sheet/workbook, include a total/subtotal field.
Name the range (Insert>Name>Define or just type the name in the Address box and hit Enter)
The name should be unique to that workbook. TomsTotal, EasternSales, etc.
Now you can create a formula on the accumulator sheet any time you want.
=April98!TomsTotal +April99!TomsTotal

With that amount of data, Access is really the correct application to use.
I might suggest using Access to store the data and then use Excel as a "Front end" to pull out numbers for reports, etc.
Take a look at Data>Get External Data. You can create Queries that will bring back exactly what you need. (Month, Salesman, Sales ...)
I have a rule of thumb. 2-300 records, I can use Word if I want. 2-3000 records use Excel. Over about 2000 records, Access is the best choice.
Send me a note, and I'll get you started.

by stillatit

One answer is to NOT rename the files, but rather have the files with constant names in separate directories, which are named for the batch number. If the master is in the same directory, the link should be relative rather than absolute.

Another answer is to develop a macro which copies the values of your answers (not references) into a small, separate spreadsheet, which you can then email to your director. If you format it right, your dirctor might be able to just copy one row into the master spreadsheet.

Good luck.

This discussion is closed to new replies.