linking data between excel documents (excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

Second attached file! The Summary file

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

C. Canuck,
Access would do the trick. But the learning curve is between steep and brick wall

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

Reply | Quote

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

Reply | Quote

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 ActiveCell.CurrentRegion.Copy 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 SumIf 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 SumIf formulas to get individual subtotals. (Funny, even when I set the Advanced Filter for unique values, one job is doubled. Don’t know why.)

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

Errol

Reply | Quote

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!!!!!

Reply | Quote