Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automating Excel (2003)

    I'm not sure how to even ask my question. I know the subject line is very vague.

    I receive three workbooks from three different people monthly. I use data from these three workbooks in a worksheet that I use to create stats for safety criteria. I cannot figure out a way of automating the cherry picking I do from those three spreadsheets. I spent last year adding the linking info from each new workbook sent to me to my worksheet. There has to be a better way. I know that my description isn't very good. I don't want this to become "War and Peace".

    Gasman

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: automating Excel (2003)

    Welcome to Woody's Lounge!

    I appreciate that you wanted to keep your first post brief, but it doesn't give us much to go on. Can you give us some idea of the structure of the workbooks and worksheets you're working with? Thanks in advance.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Excel (2003)

    Gasman,

    As Hans replied, having an idea of the structure/layout of the workbooks and your worksheet would be helpful.

    We do things similar in nature in that we have multiple monthly reports from various sources that are mined for data that is fed into a report. We use VBA to access the various WB's and to return the data.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Excel (2003)

    You could have a table that aggregates the information, each row of data being one month. The last row with data being the one containing direct formulas to the workbook(s).
    When a next month is due, you copy the formula row one row down and copy/paste-special values the row above it.
    Then you go to Edit, Links and change the source of the link(s) shown to the new month's files. Done.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Lounger
    Join Date
    Jan 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Excel (2003)

    Sorry for taking soooo long to get back to all of you.

    I decided to take a very unique path to solve this. I went to the person that creates the workbook and asked them to add one column to summarize the information I wanted. I think that we sometimes forget how to just talk to each other and ask for help from our co-workers.

    Thanks for you attention.

Posting Permissions

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