General Excel Question

[robfo0]

I have a question. I havent actually started working on this project yet because i wanted to see how it would work, I'm sure someone here has done it before.

Its regarding links in an excel file to other excel files. I have an excel file for each customer (about 3,000 files currently). I also have a database with all of the names in it. What i would like to do is make a report of each customer. I would like the report to contain certain information from each customer file (I probably wont be collecting data from all 3,000 files, but maybe files from this month). My idea was to create a link from my main database of names to each customer file. My question is: Is this a good idea? would updating all the links cause instability in excel? Is there a better way to do this? I also thought about possibly making a macro that opens each file and gets the data. Would this be a better solution?

I think access would be a better option for me right now, but its a bit late for that and i have no idea how to use it

Thanks very much.

 

[Jay Petrulis]

Hi,

My 2 cents...

Access would be a better option, although it would be an intensive data entry task.

I wouldn't recommend the links option because that might cause the recalc time to slow to a crawl. Hyperlinking might require you to store all the files in a certain folder and not move and/or change the names.

Your best non-database bet might be to use a macro which prompts you for the files to load/data to retrieve at runtime (or automatically if criteria can be discerned.

Regards,
Jay

 

[Legacy 98055]

Hi.

I work with large numbers at work and have found a good alternative to Access. I am not familiar with Access either...

I am assuming that each sheet is set up the same way for each customer?

If this is the case, I would create a macro which would store each individual customer's info in a regular text file.

Then you could create another macro to recall based upon whatever criteria you wish.
Customer Name, ID number, Phone Number, ect...

If you want, send me one of the 3000 sheets and I'll write a macro which will convert each seperate sheet into a smaller, much faster file.

You will then have one sheet in Excel to display all of your customer info...

It's basically a customizable database with alot less overhead then 3000 seperate sheets.

TsTom@Hotmail.com

Good Luck!

Sounds like a fun little project

 

[Tommy Bak]

Hi
you could make a macro, so when you put an X beside a costumer, it would automatic create the links to certain cells in the specific customerfile.
This way you will limit the amount of links.
This can only be made if you know exactly path / filename for each customer.

regards Tommy