Monthly Summary and SUMIF

[General Specific]

I have created a monthly project labor tracking workbook. It is set up
with one page per day named 11-1-05, 11-2-05, etc and a summary page
formatted as follows:

11-1-05

Designation Total
Project Manager $1,481.25
Project Supervisor $5,604.38
Skilled Labor $9,360.00
Project Labor $10,806.25
Consultant $973.75

Total $28,225.63


11-2-05

Designation Total
Project Manager $1,481.25
Project Supervisor $5,066.25
Skilled Labor $9,326.25
Project Labor $6,402.50
Consultant $973.75

Total $23,250.00


and on down through November.



The totals for each designation are calculated using SUMIF:

=SUMIF('11-1-05'!C10:C78, "PM", '11-1-05'!O10:O78)

This is simply looks at the page '11-1-05" and searches for the code
"PM" and returns the total of associated entries found in the range
O10:O78.


Is there any way to have the formula get the page name (eg 11-1-05)
from the date entered in the upper left of each daily totals group?

This would allow us to apply this summary page to other monthly labor
workbooks without having to rewrite all the formulas.


TIA

 

[Pete]

A procedural way to do it would be to copy the summary block down for
the next day and just use Search/Replace (CTRL_H), replacing 11-2-05
with 11-3-05 and so on for each day required.

Have you tried the INDIRECT function? I think the following might work,
assuming your date is in cell A10:

=SUMIF(INDIRECT(A10 & "!C10:C78"), "PM", INDIRECT(A10 & "!O10:O78"))

You might need to add the apostrophes around the A10, i.e. ("'" & A10
&"'!C10:C78")

Pete

 

[Pete]

A procedural way to do it would be to copy the summary block down for
the next day and just use Search/Replace (CTRL_H), replacing 11-2-05
with 11-3-05 and so on for each day required.

Have you tried the INDIRECT function? I think the following might work,
assuming your date is in cell A10:

=SUMIF(INDIRECT(A10 & "!C10:C78"), "PM", INDIRECT(A10 & "!O10:O78"))

You might need to add the apostrophes around the A10, i.e. ("'" & A10
&"'!C10:C78")

Pete

 

[Pete]

Brad,

It will pick up the page which is defined in cell A10. If you then copy
the
block of formulae down, say 12 rows below, then the "A10" would become
"A22", which is where you would put the next date, and similarly for
the
block below that referring to a date in cell A34 etc.

Pete

 

[Ken Wright]

Do you really need them on different pages? If you kept all your data on a
single sheet, you could then use a Pivot Table to query it, and Data /
Filter / Autofilter as well if you wanted. You would find you had so much
more analytical power at your fingertips by doing it this way.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

All kinds of options open to you if you have your data stored in the correct
format.

Dynamic ranges could ensure your Pivot tables expanded their source data
entry ranges as you added more data

http://www.contextures.com/xlPivot01.html

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[General Specific]

Do you really need them on different pages? If you kept all your data on a
single sheet, you could then use a Pivot Table to query it, and Data /
Filter / Autofilter as well if you wanted. You would find you had so much
more analytical power at your fingertips by doing it this way.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

All kinds of options open to you if you have your data stored in the correct
format.

I'll have to think about that.

I put the daily data on separate pages so that the pages can function
as reports.

I suppose I could put raw data on a single page and still create daily
reports from that data.

Has anyone here tried Crystal Reports? A friend suggested it.

 

[Ken Wright]

Pivot tables will give you similar functionality to the reports you will get
out of Crystal reports.

Regards
Ken.....................