working with data from several worksheets.

[Jason Cohen]

I have a file containing several woksheets. Each worksheet pertains to a
specific aspect of healthcare (Doctor, Insurance, Pharmacy, etc.). There is
one final worksheet that is nothing more than a summary...which re-displays
the grand totals of each worksheet within this file. Here is the problem:

I have a column that names the family member who the item pertains to, and
on the summary worksheet I would like to gather the grand total for all
worksheets for each family member. It happens to be that all worksheets in
this fil has column "E" as the column that lists the name of the family
member. So...is there a formula that can gather all expendatures from all
worksheets in the file and total them up for me?

Thanks for any help!

[K Dales]

Yes: Let's say you are on row 2 and each sheet has the totals you want to
tally in column F, and you also want the grand total for the family in column
F of your summary sheet; so in cell F2 you can use
=SUMIF(Doctor!E:E,E2,Doctor!F:F)+SUMIF(Insurance!E:E,E2,Doctor!F:F)+SUMIF(Pharmacy!E:E,E2,Doctor!F:F)+...

This is written so you can copy it down on your summary sheet without having
to change cell references, but it also assumes you have no other data below
your data tables on each of the sheets. If you do you can specify the actual
ranges (e.g. $F$2:$F$100) instead of the entire columns (e.g. F:F) but then
you need to be careful as you add data to the tables that you do not destroy
the referenced ranges.
--
- K Dales


"Jason Cohen" wrote:

> I have a file containing several woksheets. Each worksheet pertains to a
> specific aspect of healthcare (Doctor, Insurance, Pharmacy, etc.). There is
> one final worksheet that is nothing more than a summary...which re-displays
> the grand totals of each worksheet within this file. Here is the problem:
>
> I have a column that names the family member who the item pertains to, and
> on the summary worksheet I would like to gather the grand total for all
> worksheets for each family member. It happens to be that all worksheets in
> this fil has column "E" as the column that lists the name of the family
> member. So...is there a formula that can gather all expendatures from all
> worksheets in the file and total them up for me?
>
> Thanks for any help!

[Jason Cohen]

The family members are not each on their own ror or column. Here is how i
have it:
Each row is listested in order by date. The five colums are in this order:
Date, Company Rendering Service, Amount Paid, Method of Payment, Family
Member the service was for.
My file has one sheet for doctor_dentist, one for hospital_lab, one for
insurance, and one for pharmacy. There is a final sheet that brings together
the grand total of all the sheets, and I have no trouble with that. My
trouble is that I want to total each family member's lines from all the four
sheets. Can I do this?

"K Dales" wrote:

> Yes: Let's say you are on row 2 and each sheet has the totals you want to
> tally in column F, and you also want the grand total for the family in column
> F of your summary sheet; so in cell F2 you can use
> =SUMIF(Doctor!E:E,E2,Doctor!F:F)+SUMIF(Insurance!E:E,E2,Doctor!F:F)+SUMIF(Pharmacy!E:E,E2,Doctor!F:F)+...
>
> This is written so you can copy it down on your summary sheet without having
> to change cell references, but it also assumes you have no other data below
> your data tables on each of the sheets. If you do you can specify the actual
> ranges (e.g. $F$2:$F$100) instead of the entire columns (e.g. F:F) but then
> you need to be careful as you add data to the tables that you do not destroy
> the referenced ranges.
> --
> - K Dales
>
>
> "Jason Cohen" wrote:
>
> > I have a file containing several woksheets. Each worksheet pertains to a
> > specific aspect of healthcare (Doctor, Insurance, Pharmacy, etc.). There is
> > one final worksheet that is nothing more than a summary...which re-displays
> > the grand totals of each worksheet within this file. Here is the problem:
> >
> > I have a column that names the family member who the item pertains to, and
> > on the summary worksheet I would like to gather the grand total for all
> > worksheets for each family member. It happens to be that all worksheets in
> > this fil has column "E" as the column that lists the name of the family
> > member. So...is there a formula that can gather all expendatures from all
> > worksheets in the file and total them up for me?
> >
> > Thanks for any help!