MultiSheet Macro- sum from one display in another

[MsMope]

Location:
Posts: 1
MultiSheet macro, to sum based on condition
I have a three sheet workbook, one sheet is a summary sheet, the second is a data sheet and the third contains the constants.
I need to sum information from sheet2 and display in sheet1, sound simple enough. I am summing the total plant hours.

I have several different order types, stored in a column in sheet2, these order types need to sum into different cells on sheet1. I also need to have the amounts sum based on a date input from user(by week). I have been thinking to select a variable range, because the sheet will be constantly added too, and then writing a loop to go thru the date column, but how do I pull the information?
Any suggestions?
I tried to attach the file, but it was too large, if anyone wants it let me know I can e mail it.

Thanks,
msMope

 

[SkipVought]

msMope,

Sounds like a job for...

The PivotTable Wizard

Have you checked out PivotTable functionality?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[MsMope]

yes I tried to use a pivot table, but it told me that I had to much data, and it crashed. I will try again though.
Thanks,
Rayna

 

[SkipVought]

How much data are we talking about?

What is the structure of the table?

Exactly what needs to be reported and summarized by what?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[MsMope]

Each house order has it's own line in the data part, so approx. 5000-7000
The structure is :
Production Date
Plant
Product Type
Set Date
SAP #
Builder SE
Model
Floor Type
Estimated Arch Design Hours
Arch Design Initals
Structural Wall/Floor Hours Estimated Wall/Floor Initials
Structural Truss Engineer Hours Estimated Truss Initials
Window P.O. #
Window Deliv. Date
Total Production hours
Wall Floor Hours
Truss Hours
Truss Hours Plant 11
Truss Hours Plant 12
Truss Hours Plant 15
Truss Hours Sub
Date Firm
Date in Requirements
Date Cln
Date Released
Requested by
No of Cores
Elec
Lights
Install Insul
WP
No of Amends
W/F Hours % based on Shop Production
TRS Hours % based on Shop Production
Actual Amend Hours
Packed by
Square Footage
Notes
Standard Engineering hours
Actual Arch Engineer Hours
Arch Engineer Effieciency
Actual Wall/Floor Hours
Wall/Floor Effiency
Actual Truss Hours
Truss Engineer Effiency
Total Hours Estimated
Total Hours Actual
Hours Saved
Total % hours saved
Those are the columns, the rows are sales order numbers, I need to sum the estimated hours, and actual house by week, by plant, by product type.
Thanks
RAyna

 

[SkipVought]

I think that you have alot of useless data.

You might want to try to use MS Query -- Data/Get external data -- Excel Files -- YOUR WORKBOOK -- your table/sheet -- and eliminate the unnecessary column and SUM the columns required, GROUP BY the columns you are not summing and ORDER BY week, plant, product type

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244