# Adding values from one sheet to the next

I have a workbook with 10 sheets starting with March and running thru December for 2012.

If I want each successive sheet to maintain a YTD total column, then what should the formula look like?

For example, if I need a formula to add cell C14 from March and cell C16 from April then what is the proper syntax?

-

The way I handle this is to write a formula that sums "vertically", through the workbook. If you think of a normal Excel formula as being in 2D, this makes it 3D.

To do this you would need to use a consistent cell to store your info. If you have a worksheet for each month, pick one cell for your monthly total and keep it the same for every month. Let's say we choose cell C14.

Here's the formula you need to calculate YTD for December:

``````=SUM(Jan:Dec!C14)
``````

I'm assuming a couple of things here.

This assumes your sheets are named Jan, Feb, Mar, Apr etc, and also that they are in that order within the workbook (ie the order is Jan, Feb, Mar, Apr etc).

For each worksheet you'll need to tweak the formula, ie:

``````=SUM(Jan:Jan!C14)
=SUM(Jan:Feb!C14)
=SUM(Jan:Mar!C14)
=SUM(Jan:Apr!C14)
etc
``````
-

I don't think you need anything complex to accomplish what you want.

You can set the YTD cell for March to be the sum of whatever it's counting up on that sheet, then have April's YTD cell be another sum formula that starts with March's YTD column and adds the sum of April's count. Repeat this with each successive month/worksheet and you've got your running total.

Essentially you'd have:

• March's YTD =sum(B2:B6)
• April's YTD =sum(March!A1, B2:B6)
• May's YTD =sum(April!A1, B2:B6)
• etc
• etc
-

To reference a cell in a different sheet, you can use the '!' syntax:

``````=OtherSheet!C14
``````

Note that if the sheet name contains spaces, you'll need to single-quote it:

``````='Other Sheet'!C14
``````

So, for your example, if your sheets are simply named with the month names, that'd be:

``````=SUM(March!C14,April!C16)
``````
-