Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking data between excel documents (excel 2000)

    Okay, here goes.....i have been staring at this for some time and have become suddenly brain dead. I have a simple request from a project manager who wants to use excel to report information around tickets. A ticket represents a piece of IT work to be done within the project. Each employee has been given a simple spreadsheet to fill out each week (see attached sample called sally timesheet). I have put some comments in this sheet to explain what happens (or should happen). Then there is the PM's worksheet (see attached Project Summary Tickets). There are multiple worksheets, some are simply references and then there are two (current and archive). Each of these two sheets has some more comments within that describe what the PM wants to see. If anyone can provide some insight how to make excel documents share information, lists, etc i would greatly appreciate it.

    Thanks

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 2000)

    Second attached file! The Summary file

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 20

    At a quick glance, it looks as if this is not really an Excel project. My instinct is that that, if you do try to set it up that way, the summary files will soon become so creaky with links that they will perorm like molasses.

    I would suggest that you keep Excel as a front end for input and reports and use Access as a back end. There is an alternative of setting up pivot tables, but the data possibilities seem a bit intense.

    HTH
    Gre

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 20

    That is what i wanted to use for this. ACCESS would be much better but the PM doesn't have it installed and she will not get the funding to have a license bought. She is also not familiar with ACCESS so we are trying to make do with what has has and knows. I do realize the potential risk, this is a very bad way of doing this. Would it improve if all the information was in one excel document and then she can use filters etc to get the results?

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

    Re: linking data between excel documents (excel 2000)

    I agree with unkamunka that the data should go into a database, Access for example. I would go even one step further and suggest to do the data entry in Access too, if possible. Reporting can be done in Access, or in Excel (linked to the data in the database).

    Excel's multi-user features are so-so only, and although those of Access are not perfect, they are a lot better than those of Excel.

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

    Re: linking data between excel documents (excel 20

    I fear that having multiple users updating multiple workbooks, and collecting all this information in a single workbook will be a nightmare, and having them update a single workbook will be a nightmare of another kind.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 20

    If you have Access installed, you can turn around the details and give them back to her in Excel format. It sounds to me like she may simply be looking for a series of reports - or are there going to be too many "what ifs"?

    The alternative I was suggesting was that all the data was in one or two tables that could be used for Pivot Table reports. I could be wrong, but it seems that Filters would not enable enough three-dimensionality.

    HTH
    Gre

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 2000)

    C. Canuck,
    Access would do the trick. But the learning curve is between steep and brick wall <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Please let me know if this is the direction:
    1. Collect all timesheets the dozen worker bees send in weekly.
    2. Somehow the individual lines from each timesheet get transferred to the PM summary.
    3. Finally the PM can use Excel's Data Filter to pick out projects? (To see what each person has contributed & the time spent, etc.) Or do you want all the projects summarized? Or both, on the "Current" PM Sheet?

    Errol

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 2000)

    Very close, The team members would enter the time they worked for the week against the tickets they were assigned to. A ticket is simply a piece of work and the ticket tracks the effort until the work is completed. Each Friday the Team member would load the rows of detail for that week, the PM would then have the timesheets for 12 staff members available to load into a single worksheet of her Summary excel document. At this point she has the full details for all team members for the week. Then she would like to be able to summarize by ticket number, then a view by team member and then a grand total of hours for the week. Once a ticket is complete (status field = Complete), then the details for that ticket should be moved to an Archived worksheet within the same Summary Excel Document.
    Hope this helps !

    Thanks

  10. #10
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 2000)

    Canuck,
    This whole procedure is more than just a quick "how to". (As many "simple requests" turn out to be.)

    I tried a few macros to get something like this:

    A. Cycle through the Employee time reports, copy and paste the values into the PM "Current" worksheet.
    Adjust the Employee reports so there's a blank row column surrounding the data (can be hidden), then use <font face="Georgia">ActiveCell.CurrentRegion.Copy</font face=georgia> to get all the Employee data, then programmatically locate the last row on the PM Current worksheet to paste (Value, not links) this.

    B. I suggest using a "Current Summary" worksheet and <font face="Georgia">SumIf</font face=georgia> to collect both Employee work time and the individual job times. You can extract the unique jobs with Advanced Filter (do this in a Macro), then place <font face="Georgia">SumIf</font face=georgia> formulas to get individual subtotals. (Funny, even when I set the Advanced Filter for unique values, one job is doubled. Don't know why.) <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    Your Summary worksheet is attached, eh?, with some additions, eh? Hope this gets you started in the right direction! Eh?

    Errol

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking data between excel documents (excel 2000)

    Errol,

    Thanks Eh! you have to say it with attitude or someone might think you were from the US midwest. Thanks for the info and i will try it over the weekend to see if i can get it to work.

    Have a great weekend - EH!!!!!

Posting Permissions

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