Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with data from several worksheets
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. The family members are not each on their own ror or column. Here is how i have it: Each row is listed 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with data from several worksheets
It can be done with sumproduct formulas. Here is an example with 2 sheets
instead of 4. The sheets are called doctor and insurance, being totaled on a sheet called summary. I place the patient name in cell A2 on the summary sheet. =SUMPRODUCT((Doctor!$E$2:$E$65536=Summary!A2)*Doct or!$C$2:$C$65536)+SUMPRODUCT((Insurance!$E$2:$E$65 536=Summary!A2)*Insurance!$C$2:$C$65536) -- HTH... Jim Thomlinson "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. The family members are not each on their own ror or column. Here is how i have it: Each row is listed 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help working between two worksheets | Excel Worksheet Functions | |||
Working Between Worksheets | Excel Discussion (Misc queries) | |||
Working with data from two worksheets | Excel Worksheet Functions | |||
working with data between worksheets | Excel Discussion (Misc queries) | |||
working with data from several worksheets. | Excel Programming |