Please Help

[Stephen via OfficeKB.com]

I have a database of different files in one specific folder. All file names are that of my Customers. Each file has muliple sheets. "Sheet 1" may be copied and named different 3 or 4 times. I would like to be able to have a seperate workbook to add a certain cell from each workbook in this folder. If "Sheet 1" has been copied, I need to add those as well. You might as well consider me new to excel. Been with it for many years, but just now trying to get more in depth. For all I know, this may not be a possible thing I am trying to get accomplished.

Thanks in advance.

Stephen

 

[Guest]

Stephen,

I'm sure this can be done with vba but I think you'd be alot better off put
this all in one workbook, in say two worksheet in a "database" format:

In row one of your "CUSTOMER" worksheet use headings like CustID,
LastName, FirstName, plus a column for each category of information about
your customers.

On a separate TRANSACTION worksheet you might want to enter each transaction
with all you customers such as CustID, Name, Date, Description, Qty, Price,
Total
Under Name you could enter a formula like =Vlookup(A2,CustomersDb,2,false)
--assuming A2 contains the first CustomerID. This formula would be copied
down col B as far as needed.
Then when you want information about specific transactions, say those with
Customer xxx, you just do a Filter (Auto or Advanced) on the Transaction
worksheet.

This is much simpler than having to find information in several different
workbooks/sheets

Excel will hold 65536 records (transactions) on a single worksheet. If you
need more room you can add another Transaction worksheet or even store your
transactions in a table in MS Access and filter (run a query) on this Access
file to get the records you want to look at.

I have such a table with 65000 records on one excel sheet. Each record
(transaction) has 40 fields (columns) so there are approx 2.5 million pieces
of data on this worksheet. It takes about 1 to 2 seconds to Find the
records I am asking for, Sort them on 6 different levels of depth, and
display them for my perusal and perform my analysis of the data.

As I look to the future, I have just transferred the data to an MS Access
Table for storage. I still use my excel criteria range to indicate what I am
searching for and how I want it sorted and displayed. And of course I still
do the analysis in excel since this is what excel does best. The user
interface and programming are also far easier to do in excel than Access so
this is the method I have come to use.

Hope this helps.

 

[Stephen Price via OfficeKB.com]

The Information that I have with the customers are that of a plan for the upcoming year. So when I have to add different sheets, that means they have more than one Plan. You can think of it as a Farmer with multible fields and multible crops. Each Plan for each crop will be different, as well as some fields with same crop may not be the same.
Each File is stored by customer name (Last, First). Each file my have the same name on one or more sheets. This is a system that has been used in my company for some time now, and to change it to only one workbook my be quite a challenge. What I am looking for is something I change in the template, and email to all locations without changing procedure.
Although, I will look into your suggestion. That was just my first concern. Even if I don't impliment this idea, I may help in the future.


Thanks for your help.

Stephen

 

[Guest]

Can you explain a little more about the Structure of your information:

You have Customers
And each customer has at least one Plan, do any customers have multi plans?

What are the components of a Plan?
Are all components required in a plan or are some optional?

Do some/all of the components have sub components? If so what are they?

Is it possible to lay out the structure on one or two worksheets without
adding any specific data? If you could do this I would take a look at it.
If interested post back and I wll give you an email address.

 

[Stephen Price via OfficeKB.com]

Ok, I will try.

Yes, each Customer has at least one plan. Some do have multiple plans. In these multiple plans, theys are setup just as the previous. Two different sheet names, same template.

All Components are the same in each plan. I have drop down boxes in a column. So each sheet doesn't always look the same. From those drop down boxes, I can choose from up to 30-50 (?) different items.

Not quite sure by your question on the sub components. Can you clearify?

Again, not sure on what you are asking on laying it out on two worksheets? Sorry.

I have to be carefull on emailing the program. This a project that has been in place for 3 years, and gets perfected yearly or monthly. This program is something that we have made internally, and isn't available to competitors. I is a really simple program, just time consuming to put together. This is something competitors in my area are not willing to tackle, yet. So, I really can't send the program to you, but possibly could send a screen shot, if that would help.

Stephen