Summarizing Budget Data

Thu, 12/17/2009 - 15:56 — BLD Controller

We have nine locations that submit their CapEx requests as part of our budget process. I take all the individual request and have one tab for each location in one workbook. I then prepare a summary tab that has columns for each month and rows for each location. I am looking for an easy way to fill out the summary tab. Especially since in the approval process it is possible to move the expeniture from one month to the a different one. I am not sure if look-up tables, pivot tables or if there is another solution to simplify this so it is more fluid. Your suggestion?

Thu, 12/17/2009 - 16:59 — jlenning

3D Sum

Hi there,

There are two recommendations: PivotTable or 3D Sum.

Pivot Table
Instead of pasting each location's data into its own worksheet, paste all of the data from all locations into one single worksheet, and make sure there is a column for LocationName or similar. Perhaps this sheet would be named something like "data" or something. Then, you can put the summary Pivot Table on its own sheet, perhaps called the "summary" sheet. Then, if you need to create one worksheet for each location, simply use the "show report filters" (Excel 2007) or "show pages" (Excel 2003) feature within the summary pivot table to have Excel automatically create a new sheet for each location.

3D Sum
Another alternative is to use 3D Sum references. This assumes that each location worksheet is in an identical format. Then, on the summary sheet you can essentially tell Excel to add say cell G10 for all location sheets. The syntax for a 3D sum is:
=SUM('FirstSheetName:LastSheetName'!CellRef)

So, in your case it could be something like:
=SUM('Location1:Location2'!B2)

I've included some additional detail about 3D sum references below.

Hopefully, one of these will work for you, but if not, please don't hesitate to email me the file and I can check it out.

Thanks,
Jeff

More on 3D Sum
A 3D Sum range is an elegant way to consolidate values from the same cell reference through multiple worksheets. These formulas are handy when we have one sheet for each department, or each division, or each sales rep, or each project, well, you get the idea. Then, you need to sum the values from all of these sheets into one consolidation worksheet. If you didn’t know about 3D sums, then you’d have to write a formula that would contain a cell reference for each sheet. This is problematic since each time a new sheet is inserted you have to update all consolidation formulas. There is a better way, and it is called 3D sums.

First of all, this formula presupposes that all of the individual department worksheets are set up identically. That means they are all exactly the same structure, and the only difference is the values. It means for example, that Office expense is always in cell C11 in all of the sheets. If not, this approach will not work. But this approach is so good that it would typically be worth spending a few moments to make all sheets have the exact same structure.

Once all department sheets have the identical structure, simply click to the worksheet that you want the totals on, and enter the formula either by typing it or by using your mouse to click.

The 3D sum formula takes the form of:

=SUM(‘FirstSheet:LastSheet’!Cell)

Where:

FirstSheet is the leftmost worksheet name
LastSheet is the rightmost worksheet name
Cell is the cell reference, like A1

If you are writing the formula by hand, then just type it and press enter.

If you are using your mouse to click, you have to perform the steps in the following order:

Type the =SUM(
Use your mouse to click the first worksheet
Hold down the shift key and click the last worksheet
Use your mouse to select the cell reference
Close the function by typing )
Press enter

—

Thu, 12/17/2009 - 18:12 — BLD Controller

When using a pivot table and

When using a pivot table and you are using dates is there a way to show a column for every month even if a month does not have data in it?

—

BLD Controller

Thu, 12/17/2009 - 18:17 — BLD Controller

Actually,I just answered my

Actually,I just answered my own question when I tried at least one date for each month with no dollar amount associated with it and the table will have a column for each month even if there are no CapEx amounts for a given month.

Jeff,

Thanks for your help,

John

—

BLD Controller

Thu, 12/17/2009 - 18:18 — jlenning

No

Nope. Unfortunately, you'll need to ensure that there is at least one record (row) in the data that with that date, even if zero balance. So, you could for example have 12 records at the beginning of the data with zero balances, one for each month. And to group those dates into months you can use the "Group" feature of pivot tables to group by month.

Also, did you receive an email notice from our website that I had responded to your post?

—