sheet! functions

K

klat7292

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!!
 
S

Sandy Mann

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.

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
K

klat7292

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

Sandy Mann

You're very welcome, thanks for the feedback.

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top