Excel - Running Totals 2

[ladyck3]

I have a checkbook spreadsheet, it has 2 sheets, one for a Main account and another for Personal funds... the actual bank account is one account by I keep funds separated and controlled on my spreadsheet.

Currently on MY sheet, I just have the total column dragged down past a point where my transactions actually end, and the BALANCE is figured using the last amount in each column.

However, I found a checkbook template on Microsoft's templates and copied the sheet to have two pages. If you go to Microsoft's template gallery and search for checkbook, its the only one that comes up.

The problem I am facing is this.... the total column is dependent on the other data in the record... if there is nothing in the line, it comes up with a VALUE error.

So, I guess I'm asking for script here (and if so, please let me know if all I have to do is press ALT-F11 and just paste the data in the editor) I would like to have, on the first page, off to the right of the actual register, one cell that has a total of both pages, the ending balance of page 1 plus the ending balance of page 2, but since the end record will change as the entries accumulate, how can I do this?

Thanks
Laurie


LadyCK3

 

[ladyck3]

Maybe someone is already working on this, but if not, I did want to bring it to the forefront once again... I sure would like to know how to figure this out...

Thanks..
Laurie

LadyCK3

 

[SkipVought]

Hi,

In the balance column you have some expression. Change as such...

=if(iserror(expression),"",expression)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

probably bett to use a zero...

=if(iserror(expression),0,expression)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ladyck3]

Sounding stupid.... Expression?

Meaning what?

If the total in F7 (via formula) is 20.00
then in row 8, I add 20.00 then number in F8 will be 40.00

I want the last number to always be the number used to calculate the total between the two pages.... am I lost or did I lose you? Or do I need a more detailed map?

Off to look for an access answer here in tek-tips.. wish me luck... searching....

LadyCK3

 

[mosmas]

ladyCK3:

If the total in F7 is 20 and F8 is 20, then in G7 for example, type in this formula:

=IF(ISNUMBER(G6),G6,0)+F7 and then copy the formula down.

The result will look like this:
Row F G
7 20 20
8 20 40
9 30 70


I hope this helps.

Moses.

 

[ChrisBurch]

You can do this using dynamic named ranges, and formulae.

Assume 3 sheets called 'Summary' 'Main' & 'Personal' with the following data.

Main

ColumnA ColumnB
1 10
2 10
3 10

Insert a named range called 'tot_main' with the range declared as
=OFFSET(Main!$B$1,0,0,COUNTA(Main!$B:$B),1

Personal

ColumnA ColumnB
1 10
2 10
3 10
4 10

Insert a named range called 'tot_personal' with the range declared as
=OFFSET(Personal!$B$1,0,0,COUNTA(Personal!$B:$B),1

Summary

In any cell use the formular
=Sum{Tot_main) + Sum(Tot_Personal) which will give a result 70.

Since we are using dynamic ranges as soon as you add new data to either sheet, it will be reflected on the summary sheet as the named ranges will expand to suit.

Regards, Chris



Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[ChrisBurch]

Sorry I left the closing brackets off the range formulas, they should look like

=OFFSET(Main!$B$1,0,0,COUNTA(Main!$B:$B),1)

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[ladyck3]

I thought I'd check my mail before going to bed, I see the posts and want to thank you for the responses...

I'm wondering if you may have downloaded the template and taken a look at it.... just to make sure I've explained it properly. Basically I copied the one sheet and added it at the end... made one Main and the other Personal.... up on the right, in cell to the right of the title area, I want a total of the last figure in the F column on both... just so I explain it properly is all...

I have to have the spreadsheet open and test what has been provided.... I'll do that in the a.m.

Thanks much!!!

La

LadyCK3

 

[ChrisBurch]

Okay, I downloaded the template and you can forget all previous advice. I can't see how you can do what you want without some vba coding. If anyone else wants to have a look at the template, and can suggest a formula method of handling the requirement, please let us all know.

I have knocked up a quick and dirty solution for your requirement, and if you would like me to e-mail it to you you can e-mail me at chrisb@abmflexibles.aust.com.

Be very careful with this workbook though, MS put a note in about problems if you skipped any rows, and they sure aren't kidding. The template is a DOG!!!!!!

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[SkipVought]

Hey laurie,

The formula in the Balance column is

=IF(AND(ISBLANK(D8),ISBLANK(E8)),"",F7-D8+E8)

which ALSO could be written

=IF(ISERROR(F7-D8+E8),"",F7-D8+E8)

which is what I originally suggested yesterday.

What is it that you need?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ladyck3]

Skip, neither of the last 2 examples work...

They work on that page but does not take into consideration the ending total, I think Chris is right it would require code simply because the cell to take into consideration is always a variable....

If I add a registry entry, the total to be considered then drops a row to the next row... if you give it either of the formulas you provided, it does not take into consideration that the total to consider has now changed to another cell, the running total ran to the next line, the formula did not.

Chris... you can use my hotmail account if you wish to send a sample... ladyck3@hotmail.com

Thing is... I have a register that I created, and have been using for awhile now but I liked the way this one works with the deposit/withdrawal columns... I just enter a positive or negative number and let the spreadsheet calculate the remaining funds... that calculation column is dragged down to infinity and then my formula adds the last cell at the end of infinity on the main register, to the last cell at the end of infinity on the personal register and that's how I get my total.

The template I found at Microsoft does not allow for that... but as Chris says... if this is truly a piece of junk, and it might be... I'll stick with what I have.

Or I might just modify mine with a positive/negative column and see what gives...

Anyway, thanks everyone....
Laurie


LadyCK3

 

[ladyck3]

MOSES... I NEED YOU... Thread702-679243

I MEAN it.... you should email me

LadyCK3

 

[SkipVought]

Laurie,

Don't believe you need VBA. How 'bout a simple offset formula? You want the balance from Col F?

=OFFSET(F5,COUNT($F$5:$F$65536)-1,0,1,1)+OFFSET(Main!F5,COUNT(Main!$F$5:$F$65536)-1,0,1,1)

where this is on the Personal sheet and referencing the Main sheet. You can reverse it on the Main sheet.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ladyck3]

HOT DIGGITY!!!

Skip yer a GENIOUS, it worked, it worked, it worked <singing> it worked, it worked, it worked...

<doing the HAPPY DANCE>

Skip rocks!!! Ya wanna dance?

hehe, its the little things.... Skip, yer a genious!!!

If I could, I'd making it &quot;Raining STARS&quot; but as it stands, I can only bestow upon you just the one!

Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Laurie


LadyCK3

 

[SkipVought]

Gooaaa

http://wwwwwwwwwww-llllllllyyyyy

Sargant Carter!



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ladyck3]

See.... I'm so easily pleased

LadyCK3

 

[ChrisBurch]

I stand corrected , it can be done with formulae!!, which is learning 1.
Learning 2 is, if you throw the gauntlet down in front skip, you will get copious quantities of egg .
Learning 3 is you can do much more with Offsets than Chris realised, so Chris had better have a closer look at them [Glasses].

Otherwise, I stand by my earlier comment, that this template is a DOG!!!

Star to skip.

Chris

It worked yesterday.
It doesn't work today.
That's Windows!