complicated function... can anyone help?

[Nimit Mehta]

Problem: I have 1000 excel workbooks maintaining database
of conference calling customers. All workbooks are in a
folder and are named unique (customer name). All sheets
have same criterias. Coloumn A in all contains DATE, B
Contains TIME, C contains COUNTRY, D contains RATE, E
contains MINUTES. Entering this manually, i get RATE x
MINUTES = Total ammount which gets deducted from customers
total balance he has in his account. I.E All workbooks
contain formulas. All these workbooks are linked with one
main sheet called "report" where i can see current balance
of all 1000 customers at one go, and their other details
without opening individual files.
What my employees do right now is manually write down on a
paper, customer name, date, time, country, his password,
and minutes he called. At the end of the day i enter this
data of about 3000 minutes everyday into individual excel
files of all customers. This is really hectic.
what i want to do is, give my employees an excel sheet and
want them to write everything they used to write on paper
in this excel sheet, details like "name, password,
country, minutes" and that should automatically go into
the individual excel files of respective customers.
Do i need to use some third party software to make this
work, or can i do this using only Excel?
Thanking You.
Nimit.

 

[Earl Kiosterud]

Nimit,

Heavens. You're saving a file for each customer. Linking all that stuff?
You must like work. Do you want to come an change my oil and mow my lawn?


How about one workbook, including a column for customer? No linking
required. Pretty straightforward data entry.

It might look like this:
Customer Date Time Country rate Minutes

People often keep stuff in separate sheets, or even separate files. Unless
there's a compelling reason, it's often MUCH better to keep it in one table.
Might not sit well with you, but consider it carefully. You'll be suprised
at how much more you can do with this. An Autofilter can reduce it to a
given customer at a click. Sorting can rearrange the data by date, time,
whatever. Great stuff.

 

[Guest]

One workbook for all 1000 customers? with 4000 minutes of
calls made everyday? with 300 off 1000 customers calling
everyday? First i had one single workbook and it started
iving me slow calculations once my customer base crossed
500. It started getting hanged on startup. Plus all my
customers need history of calls made, totalling upto 3
lakh minutes of database. All 1000 files combine to give
me size 20 MB of data. ummm, I dont think everything in
one workbook is going to help.

 

[Bill]

Judging from what I am reading, have you considered using a database instead
of spreadsheets? Seems to be a lot easier to manage that much data, to
input (using forms), keep such data, and create reports showing details, or
just summaries, etc.