Need help with A 3-D reference type formula (real novice here)

[topaz]

I think this is what I need, but I am not sure, as I have never done anything
this involved before - using XL 2003 from MS Ofc Pro. I have a workbook with
12 simple worksheets (Jan '05 thru to Dec '05). Each WS lists their
respective months income & expenses and calculates & verifies the totals. I
then created a few simple functions to show monthly gross income, expenses &
net income on @ WS. Now I would like to be able to do a progressive
year-to-date type calculation from each of the WS - much like you would see
on a paycheck.
i.e
WS1 - cell W49 Calculates and displays sum of gross income
WS1 - cell W50 Calculates and displays sum of expenses
WS1 - cell W51 Calculates and displays the sum of the above 2 cells

WS2 uses cells W50, W51, W52
WS3 uses cells W33, W34, W35
WS4 uses cells W48, W49, W50 etc each WS seems to use different cells for
the month end totals.

So I think I would need to identify the cells I want to use i.e WS1 gross
income + WS2 gross income = Year to date gross income then the next step
would be total of WS1 and WS2 plus new value from WS3 for a new year to date
value. Just don't know how to proceed.

 

[Biff]

Hi!

each WS seems to use different cells for the month end totals.

If you setup all your monthly sheets the same this is relatively easy.

On each of your monthly sheets put your month end total formulas in the same
cell, say, A1

Then, assume your workbook tabs are laid out like this:

Summary | Jan | Feb | Mar | Apr |.....................| Dec |

To get a YTD sum from all the monthly sheets:

=SUM(Janec!A1)

Any sheets that are inserted between Jan and Dec will be included in the sum
formula.

Another popular way to do this is to insert a blank sheet before Jan and
name it Start. Then insert another blank sheet after Dec and name it End.
Like this:

Summary | Start | Jan | Feb | Mar | Apr |.....................| Dec | End |

Then use the same formula:

=SUM(Start:End!A1)

Biff