Excel, Access, or Something Else?

[John Kitchens]

Hello All,
I am using Windows 2000. I have created a program in Excel 2000. This
program is for a recycling company. The user will enter the gross weight of
a full truck of metal and then enter the empty weight of the truck.

The user selects through combo boxes a list of 25 different items (iron,
tin, brass, etc). The weight is carried to the appropriate column where the
price is calculated.

Through the use of macro's etc. the user will enter in the customer's name
and the name will be stored alphabetically.

PROBLEM #1
The user will save this file for example as T589. They will then use ticket
T589 to start with for the next customer and name it T590.

We tried making a template, but when a new customer would visit the name
would not be stored on the template because the name was entered and saved
as a file and not as a template. Is there another way that I can do this so
that user can open the template every time and have the new name appear but
without having to save this file as a template. (some users are not to savvy
and will just screw it up)

PROBLEM#2
Each time a customer comes and we purchase material from him we save the
transaction as an individual file saved in a folder (for example today it
would be saved in a folder named "082305". This folder is located in a
folder named "August" which is located in a folder named (you guessed it!)
"2005".

We have about 700 unique customers. I need a way to search through all of
the files and pull up all of the files of a particular customer.

For example "Joe Smith". If I could enter his name in a combo box or
something to that effect and retreive a list of every file that contains his
name on Sheet 1 (The customers name is listed on Sheet 1 of the workbook.
It is selected from a combobox.)

All of the 700 customers, and any new ones are listed on Sheet 3. This is a
hidden page of the workbook.

I tried using the Search feature in Windows, but this failed. The reason
being if "Joe Smith" became a customer on January 5, 2005 his name was
entered into the program that day. So when I search for any file containing
his name it returns with every file since then because his name is indeed
hidden in the name list in every file.

WHAT I NEED
I hope I have not made this too confusing. I need a way to retrieve names
that appear in the combobox on Sheet 1. I need to do this for all of the
files so of course the files would be closed.

Does anyone know if this can be done in Excel or even if it can be done in
Access or another program?

I appreciate any input that can be given.

John

[Ed]

John:

Sounds like one thing you need is an incrementing ticket number. Try here
for some ideas: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html .

To get the customer's name into the document, you can take the value of the
cell with the name and write it to a document property such as "Keyword".
Look at BuiltInProperties or Custom Properties.

HTH
Ed

"John Kitchens" <[email protected]> wrote in message
news:[email protected]...
> Hello All,
> I am using Windows 2000. I have created a program in Excel 2000. This
> program is for a recycling company. The user will enter the gross weight
of
> a full truck of metal and then enter the empty weight of the truck.
>
> The user selects through combo boxes a list of 25 different items (iron,
> tin, brass, etc). The weight is carried to the appropriate column where
the
> price is calculated.
>
> Through the use of macro's etc. the user will enter in the customer's name
> and the name will be stored alphabetically.
>
> PROBLEM #1
> The user will save this file for example as T589. They will then use
ticket
> T589 to start with for the next customer and name it T590.
>
> We tried making a template, but when a new customer would visit the name
> would not be stored on the template because the name was entered and saved
> as a file and not as a template. Is there another way that I can do this
so
> that user can open the template every time and have the new name appear
but
> without having to save this file as a template. (some users are not to
savvy
> and will just screw it up)
>
> PROBLEM#2
> Each time a customer comes and we purchase material from him we save the
> transaction as an individual file saved in a folder (for example today it
> would be saved in a folder named "082305". This folder is located in a
> folder named "August" which is located in a folder named (you guessed it!)
> "2005".
>
> We have about 700 unique customers. I need a way to search through all of
> the files and pull up all of the files of a particular customer.
>
> For example "Joe Smith". If I could enter his name in a combo box or
> something to that effect and retreive a list of every file that contains
his
> name on Sheet 1 (The customers name is listed on Sheet 1 of the workbook.
> It is selected from a combobox.)
>
> All of the 700 customers, and any new ones are listed on Sheet 3. This is
a
> hidden page of the workbook.
>
> I tried using the Search feature in Windows, but this failed. The reason
> being if "Joe Smith" became a customer on January 5, 2005 his name was
> entered into the program that day. So when I search for any file
containing
> his name it returns with every file since then because his name is indeed
> hidden in the name list in every file.
>
> WHAT I NEED
> I hope I have not made this too confusing. I need a way to retrieve names
> that appear in the combobox on Sheet 1. I need to do this for all of the
> files so of course the files would be closed.
>
> Does anyone know if this can be done in Excel or even if it can be done in
> Access or another program?
>
> I appreciate any input that can be given.
>
> John
>
>