If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combining data from multiple workbooks
I have put together a spreadsheet for our customer service department to track the TYPES of support phone calls they receive. Each customer rep has their own Workbook and each Workbook has individual Worksheets for Monday, Tuesday, Wednesday, Thursday, Friday and Weekly Totals.
They have a mix of Vista and XP machines and Excel 2003 and 2007. All we are tracking are the Type of Call (Hardware, Software, Training, etc) and the number of minutes each call takes. All is good here, so far. My question is, the Department Manager (who has Excel 2007) will receive each of the four support member's spreadsheet at the end of business on Friday. I wrote the formulas in her spreadsheet as follows: =SUM([Barb.xls]Week Totals'!C3+[Wendy.xls]Week Totals'!C3+[Gail.xls]Week Totals'!C3+[Kim.xls]Week Totals'!C3) I did this by selecting SUM and then going to each of the spreadsheets and clicking the required cell, hit the "+" sign and repeating this process for all of the staff members. Is this the right way to find the SUM of identical cell from multiple workbooks? Are there any pitfalls I need to be aware of when SUMMING data from multiple workbooks? Can I create the Manager's worksheet on my machine and have it work on hers? If I create it on my machine will the final spreadsheet look for those other workbooks on my computer? Sorry this was so long... d. |
#2
|
|||
|
|||
combining data from multiple workbooks
I'd do something like that with a few minor differences.
First, when those "sending" workbooks are closed, the formula can get pretty long--when the drive/paths are included. I'd insert a separate sheet (and hide it???) and have a dedicated cell for each value being retrieved. =[Barb.xls]Week Totals'!C3 Then I'd use: =sum(hidden!a:a) to get the total. Second, if somebody opens Barb.xls and inserts a row or column or deletes a row or column and your workbook isn't open in the same excel session. Then your workbook will still point at C3--no matter where the cell with the real data is located. One way around this is to name that cell (insert|name|define). Then you can use: =Barb.xls!DeptTotal In fact, you may want to hide a sheet in each of the sending workbooks with the key values to be returned. Then hide that sheet. Then you can refer to that hidden sheet in your hidden sheet. dslocum wrote: I have put together a spreadsheet for our customer service department to track the TYPES of support phone calls they receive. Each customer rep has their own Workbook and each Workbook has individual Worksheets for Monday, Tuesday, Wednesday, Thursday, Friday and Weekly Totals. They have a mix of Vista and XP machines and Excel 2003 and 2007. All we are tracking are the Type of Call (Hardware, Software, Training, etc) and the number of minutes each call takes. All is good here, so far. My question is, the Department Manager (who has Excel 2007) will receive each of the four support member's spreadsheet at the end of business on Friday. I wrote the formulas in her spreadsheet as follows: =SUM([Barb.xls]Week Totals'!C3+[Wendy.xls]Week Totals'!C3+[Gail.xls]Week Totals'!C3+[Kim.xls]Week Totals'!C3) I did this by selecting SUM and then going to each of the spreadsheets and clicking the required cell, hit the "+" sign and repeating this process for all of the staff members. Is this the right way to find the SUM of identical cell from multiple workbooks? Are there any pitfalls I need to be aware of when SUMMING data from multiple workbooks? Can I create the Manager's worksheet on my machine and have it work on hers? If I create it on my machine will the final spreadsheet look for those other workbooks on my computer? Sorry this was so long... d. -- dslocum -- Dave Peterson |
#3
|
|||
|
|||
Thanks Dave, Naming the Cell was too obvious and I should have thought of that myself. It is the drive/path in the formula that I am worried about.
If I create the hidden worksheet won't I still run into the drive/path? If so, do I just create the Manager's spreadsheet on my machine and edit it when I load it on hers? denny |
#4
|
|||
|
|||
Ok, here is my real issue I guess.
Once I create the Manager's workbook that collects data from all of the individual's workbooks and Save and Close the Manager's workbook and then re-Open the Manager's workbook, all of the formulas show Drive and Paths for MY machine. How do I create this Manager's workbook on my machine and then put it on a memory stick, put it on her machine and not have the formulas pointing to MY machine? Am I best off to just create this workbook on her machine and be done with it or is there something that I can learn here? Thanks once again... d. |
#5
|
|||
|
|||
combining data from multiple workbooks
You could run into problem with the length of a path. But you only have to
worry about the path for one file if you put each formula in its own cell. =[Barb.xls]Week Totals'!C3 =[Wendy.xls]WeekTotals'!C3 =[Gail.xls]Week Totals'!C3 =[Kim.xls]Week Totals'!C3 Are each going to be shorter than a single formula: =SUM([Barb.xls]Week Totals'!C3+[Wendy.xls]Week Totals'!C3 +[Gail.xls]Week Totals'!C3+[Kim.xls]Week Totals'!C3) If you really control your manager's workbook, then that sounds like the way to go. But I'd tell her to stop making changes and then take her current copy to edit. You wouldn't want to update your version and find out that later that she made 2 hours worth of changes that would be lost--or that you'll have to make your changes to her workbooks (again!) and lose the changes you just made. Multiple copies of the same workbook (er, data) is never a good thing in my opinion. dslocum wrote: Thanks Dave, Naming the Cell was too obvious and I should have thought of that myself. It is the drive/path in the formula that I am worried about. If I create the hidden worksheet won't I still run into the drive/path? If so, do I just create the Manager's spreadsheet on my machine and edit it when I load it on hers? denny -- dslocum -- Dave Peterson |
#6
|
|||
|
|||
Dave, indeed this is the formula I will be using, though there are 15 of these formulas, one for each Call Type.
=SUM([Barb.xls]Week Totals'!C3+[Wendy.xls]Week Totals'!C3 +[Gail.xls]Week Totals'!C3+[Kim.xls]Week Totals'!C3) If I do create a worksheet that holds cell data like you have here, =[Barb.xls]Week Totals'!C3 =[Wendy.xls]WeekTotals'!C3 =[Gail.xls]Week Totals'!C3 =[Kim.xls]Week Totals'!C3 I will still have the long formulas due to the Drive/Path issue, correct? The advantage you are saying is that I can then use the values found on this worksheet in the formulas of the Managers Weekly Totals worksheet, correct? There is no way around the Drive/Path issue I am assuming. The Manager isn't making changes to her spreadsheet; I only noticed that once I had everything working on my machine and Saved and Closed all of the individual workbooks and then opened them all back up to see if it would really work as I imagined, it did work correctly. However, I also noticed that the formulas in the Manager's workbook included the Drive/Path to my computer and I was looking for a way to avoid that. I agree, multiple copies of the same workbook is never a good thing. So, once the manager receives all of the workbooks from her people, I will edit the formulas in her workbook so that they point to her Drive/Path. Thank you so much for the input. You've helped me learn something and have made my spreadsheets much cleaner and more professional. d. Last edited by dslocum : April 16th, 2009 at 07:17 PM. Reason: make the post clearer |
#7
|
|||
|
|||
combining data from multiple workbooks
This formula:
=SUM([Barb.xls]Week Totals'!C3+[Wendy.xls]Week Totals'!C3 +[Gail.xls]Week Totals'!C3+[Kim.xls]Week Totals'!C3) will be more than 4 times longer than this formula: =[Barb.xls]Week Totals'!C3 Just because the drive/path will show up 4 times as often. And if each of those small formulas are too long, then there is no chance that that =sum() formula will work. Create each of those formulas and close those workbooks and watch what happens to the =sum() formula. ========= I don't know enough about how your network is setup, but if you can put all the files on a common network share, then your life will be a little easier. dslocum wrote: Dave, indeed this is the formula I will be using, though there are 15 of these formulas, one for each Call Type. =SUM([Barb.xls]Week Totals'!C3+[Wendy.xls]Week Totals'!C3 +[Gail.xls]Week Totals'!C3+[Kim.xls]Week Totals'!C3) If I do create a worksheet that holds cell data like you have here, =[Barb.xls]Week Totals'!C3 =[Wendy.xls]WeekTotals'!C3 =[Gail.xls]Week Totals'!C3 =[Kim.xls]Week Totals'!C3 I will still have the long formulas due to the Drive/Path issue, correct? The advantage you are saying is that I can then use the values found on this worksheet in the formulas of the Managers Weekly Totals worksheet, correct? There is no way around the Drive/Path issue I am assuming. The Manager isn't making changes to her spreadsheet; I only noticed that once I had everything working on my machine and Saved and Closed all of the individual workbooks and then opened them all back up to see if it would really work as I imagined, it did work correctly. However, I also noticed that the formulas in the Manager's workbook included the Drive/Path to my computer and I was looking for a way to avoid that. I agree, multiple copies of the same workbook is never a good thing. So, once the manager receives all of the workbooks from her people, I will edit the formulas in her workbook so that they point to her Drive/Path. Thank you so much for the input. You've helped me learn something and have made my spreadsheets much cleaner and more professional. d. -- dslocum -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|