Excel General Excel Question :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

General Excel Question
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

robfo0
Board Master


Joined: 20 Feb 2002
Posts: 236
Location: Stockton, California

Status: Offline

 Reply with quote  

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 icon_smile.gif

Thanks very much.

Post Wed Mar 20, 2002 10:18 pm 
 View user's profile Send private message Send e-mail ICQ Number

Jay Petrulis
MrExcel MVP


Joined: 18 Mar 2002
Posts: 1674
Location: Chicago, IL USA
Flag: Blank

Status: Offline

 Reply with quote  

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

Post Wed Mar 20, 2002 10:32 pm 
 View user's profile Send private message

TsTom
MrExcel MVP


Joined: 19 Mar 2002
Posts: 2845
Location: Cincinnati, Ohio
Flag: Blank

Status: Offline

 Reply with quote  

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

Post Thu Mar 21, 2002 10:16 am 
 View user's profile Send private message Send e-mail AIM Address

Tommy Bak
Board Master


Joined: 26 Feb 2002
Posts: 283
Location: Guderup, Denmark
Flag: Denmark

Status: Offline

 Reply with quote  

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

Post Thu Mar 21, 2002 11:58 am 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.