General Excel Question

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: General Excel Question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com