combining data from multiple workbooks

[dslocum]

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.

 

[Dave Peterson]

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.

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]

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

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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.

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.