sheet! functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

klat7292 - 15 Sep 2007 15:02 GMT

I am trying to correspond a running balance on a sheet to correspond to a
master sheet of totals. I am creating a budget workbook in Excel 2003 with
the feel of envelope budgeting where I create multiple "envelopes" (using a
check book register template) and I distribute monies from income throughout
these "envelopes". As I spend money, I enter these transactions on the
appropriate envelope sheet and MAIN checking acct sheet, but I also have a
MASTER BUDGET SHEET at the beginning of the workbook that I would like all
corresponding running envelope balances to reflect as I make
transactions...So far I have managed to create functions for the MASTER
BUDGET SHEET for income in and out, but I am stuck on "seeing the whole
picture" on that sheet with running totals from other sheets...I hope that
there is a way, because I have been doing this on an online software and it
is pretty neat, but I am trying to duplicate it in an Excel spreadsheet to
cut costs of membership. Great program, but it does have its flaws, so I am
trying to take matters into my own hands, so to speak...

Please help!! I am soo close!! I know that Excel 2003 is capable of doing
this!!

Reply to this Message

Sandy Mann - 15 Sep 2007 15:44 GMT

To get the sum of the last cell in the totals column,(Column C in my
example) try the formula:

=SUM(LOOKUP(99^99,Sheet1!E2:E500),LOOKUP(99^99,Sheet2!E2:E500),LOOKUP(99^99,Sheet3!E2:E500))

This formula will return #N/A if ANY of the sheets has no data in Column C
so make sure that if there is no data there is at least one zero in the
column with a formula like:

=IF(COUNT(C2:D2)=0,0,C2-D2)

in Row 2 and:

=IF(COUNT(C2:D2)=0,"",C2-D2)

in the rest.

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>I am trying to correspond a running balance on a sheet to correspond to a
> master sheet of totals. I am creating a budget workbook in Excel 2003 with
[quoted text clipped - 18 lines]
> Please help!! I am soo close!! I know that Excel 2003 is capable of doing
> this!!

Reply to this Message

klat7292 - 15 Sep 2007 16:24 GMT

Thanks Sandy Mann...after playing with the template, I think that I am
figuring it out...many thanks!!

> I am trying to correspond a running balance on a sheet to correspond to a
> master sheet of totals. I am creating a budget workbook in Excel 2003 with
[quoted text clipped - 14 lines]
> Please help!! I am soo close!! I know that Excel 2003 is capable of doing
> this!!

Reply to this Message

Sandy Mann - 15 Sep 2007 16:32 GMT

You're very welcome, thanks for the feedback.

Signature

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> Thanks Sandy Mann...after playing with the template, I think that I am
> figuring it out...many thanks!!
[quoted text clipped - 26 lines]
>> Please help!! I am soo close!! I know that Excel 2003 is capable of doing
>> this!!

Reply to this Message