Hi smc,
Some errors:
Master Dept. Rept. / cells F74:K74 each have circular references. Instead of
Code:
=SUM($F$66:$F$74), etc. in those cells (which include themselves in the sum), put:
[code]=SUM(F$66:F$73)
in cell F74 (note last cell is now F$73 and the removal of some of the dollar signs) and copy to the other total cells G74:K74. You could use the fill option instead of copying for the same result (grab the little handle/square on the lower right corner of the cell and drag to the right to duplicate the formula for the other cells).
Same problem on row 92 totals. Same solution.
In the Field Report 1 Details worksheet, row 22, same problem, same solution.
In the Field Report 2 Details worksheet, row 22, same problem, same solution.
As to your question about formula for distributing the project amongst quarters, it looks to me like your data doesn't fit the simple concept of splitting 20,000 50/50 between two quarters, but that your projection is based on specific line item projects/units posting in various quarters.
If I am right, here are two solutions for you. First a way to do it with your current spreadsheet design. Second a recommendation for the future.
============
I. Create separate columns for Q1/2/3/4 in the Dept Spending details, take their sum, and in the Field Rept Summary Rollup, Create Q1/2/3/4 columns, use a formulas that refer to the sums for each dept; in your master report, in your Q1/2/3/4 columns, refer to the Q1/2/3/4 budget items from Field Reprt 1 and 2. This all if I understand your accounts right.
In your Dept Spending Detailed line items, to split those numbers out to the separate columns, Use a formula in the new Q1/2/3/4 cells that refers back to the Projected Qtr and Projected total cells to extract the total if the quarter matches the quarter of the column.
EXAMPLE FORMULAS. Cell references based on Field Report 2, Dept Spending Details, General Expense section, with line items starting from row 30, Projected Total in column E, and Quarter in column F. Assumes the new Q1, Q2, Q3, and Q4 columns are columns G-J.
1. Less advanced version of formula. The formula references will stay valid with the addition of other rows and columns later:
G30 (Q1): [code]=IF($F30=1,$E30,"")
H30 (Q2): [code]=IF($F30=2,$E30,"")
I30 (Q3): =IF($F30=3,$E30,""
[/code]
J30 (Q4): =IF($F30=4,$E30,""
[/code]
Highlight G30:J30 and copy, then paste for all other line item rows on both Field Reports.
2. More advanced formula. The formula references will stay valid with the addition of other rows and columns later, as long as the 4 quarters are in the 4 columns exactly to the right of the column specifying the quarter (F at the moment). Paste the following in cell G30 (the new Q1 cell for line detail item 1.1:
=IF($F30=COLUMN(G30)-COLUMN($F30),$E30,""
[/code]
Then Fill or Copy/Paste that formula to all other Q1-Q4 cells for all dept details on both Field Reports. The formula should find the right number for the right column for everything.
----
In the total line for each Dept Spending Details section, create a SUM for each quarter. e.g. in Field Report 2, cell G38 (Q1) you could put =SUM(G$30:G37) and copy that formula to the other total for section 1 (row 38).
You now have Q1/2/3/4 numbers at every level and can build your summaries by adding the right numbers up.
Optionally hide the new Q1-4 columns when done building your formulas.
==============
II. For the future.
I'm years out of practice here, so don't take this advice as absolute, but --
When you plan your report, remember that there are two levels to things. There is the presentation level, and the raw data level. As with the example formulas above, you can see it's much easier to get your Q1, 2, 3, 4 totals, if you have Q1, 2, 3, 4 details at the levels that you need to summarize.
Another way to summarize all this data in a very flexible way would be to first create a "flat database" of all the items. Use 1 row for each detail item (at the finest grain of interest) and 1 column for each piece of info about that item. Department, Budget item, amount, etc. Often you can get the raw data you need straight from accounting or sales databases/reports, and use other Excel features to re-dice it into the reports you want.
You can use the Excel outlining feature to create an in-place report in the data table that will have subtotals and grand totals.
You can use Pivot tables and charts to create separate reports on the data that slice and dice it in different ways.
You can create query tables (more advanced) to analyze, combine, slice and dice the data in innumerable ways.
The way your reports are laid out now, there is a lot of room for error. If you can use the Outline, grouping, and subtotals features rather than the many individual tables of data there is less labor in maintenance and you can feel more confident in the aggregates (or even the details if you are hand entering them).
If you have your raw data in the file, you can either use the features above to summarize it, or you can use database and lookup functions to extract data, and conditional SUMs/COUNTs (SUMIF, SUMIFS, COUNTIF, COUNTIFS) or statistical functions to summarize data that meet certain conditions, and similarly, to do more sophisticated extraction, summary, analysis and calculations based on the data, you can use SUMPRODUCT and array formulas. Once comfortable with some of these types of formulas, you can get Excel to "do the work for you" and eliminate a lot of labor, quality control, and manual calculation time.
Hope this all helps
Asa
