I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

Gr8 Dane;222593 Wrote:
I have an open workbook with mulitiple sheets. I want to create a column
"F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then
type
it in to "F". What can I do to have this done automatically? I am
falling way
behind in doing this manually and need to catch up/remain current on
50+
pages, and counting.

Something like: enter in f12 =f11+e12 and pull down as needed

If you want cells to stay empty if there is no value in col E :
if(e12="","",f11+e12) and also pull down as needed


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61491

Kindly stop the repeat and the multi-postings.
Your query went through.

Here's one response posted earlier:

You could try this for col F
In F2: =E2
In F3: =IF(E3="","",SUM(F2,E3))
Copy F3 down as far as required

If above helps, press the YES button below to "high five" this response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Gr8 Dane" wrote:
I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

Sorry about the repeat Questoins. I got an error message saying that it did
NOT go through so I tried again. Apparently it really was going through after
all. Anyways, I tried your response, and it worked for the first cell. After
that I receieve that same answer every time. Is there a way for it to
automaticall change the new total?

"Max" wrote:

Kindly stop the repeat and the multi-postings.
Your query went through.

Here's one response posted earlier:

You could try this for col F
In F2: =E2
In F3: =IF(E3="","",SUM(F2,E3))
Copy F3 down as far as required

If above helps, press the YES button below to "high five" this response.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Gr8 Dane" wrote:
I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

Gr8 Dane wrote:
I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.


Try putting this in F2 =Sum($E$2:E2) and copy down. Formula in F3 will
be =Sum($E$2:E3) etc. Is that what you wanted?
Hope that helps.

So, either way I do this, I will still have to type something into "F",
whatever the formula? I can't just type in the new transaction into "E" and
be done?

"Pecoflyer" wrote:


Gr8 Dane;222593 Wrote:
I have an open workbook with mulitiple sheets. I want to create a column
"F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then
type
it in to "F". What can I do to have this done automatically? I am
falling way
behind in doing this manually and need to catch up/remain current on
50+
pages, and counting.

Something like: enter in f12 =f11+e12 and pull down as needed

If you want cells to stay empty if there is no value in col E :
if(e12="","",f11+e12) and also pull down as needed


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=61491

It should work fine ..

a. Check that calc mode is not set to manual:
Click Tools Options Calculation tab
Check "Automatic" OK

b. If it isn't calc mode, then your data in col E probably contains text
nums, not real nums (or a mix). Convert the source data at one go to all real
nums like this. Copy any empty cell, select col E, right-click paste
special check "Add" ok. That should do it ok, and all should compute
properly now.

p/s: Remember to "high five" ALL responses which help, press the YES buttons
below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Gr8 Dane" wrote:
Sorry about the repeat Questoins. I got an error message saying that it did
NOT go through so I tried again. Apparently it really was going through after
all. Anyways, I tried your response, and it worked for the first cell. After
that I receieve that same answer every time. Is there a way for it to
automaticall change the new total?

You could use a workbook event procedure

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("E2:E1000")) Is Nothing Then
Target.Offset(0, 1) = Target + Target.Offset(-1, 1)
End If
Application.EnableEvents = True
End Sub

Paste this code in the workbook module
I started the code at E2, if you were to tart it at E1 you would get
an error as there is nothing above F1

Where's the workbook module
http://www.contextures.com/xlvba01.html#Workbook

Ok. Thank you all for your replies. It looks like I'll be copying the Formula
and pasting it into every cell in "F" as well, unless I'm misunderstanding
something. It does add the current "E" into a new total, but i still have to
manually paste the formula into each "F" cell to get the new total. At least
I dont have to do the math too.

"Lynz" wrote:

Gr8 Dane wrote:
I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.


Try putting this in F2 =Sum($E$2:E2) and copy down. Formula in F3 will
be =Sum($E$2:E3) etc. Is that what you wanted?
Hope that helps.

On Feb 10, 2:59*am, Gr8 Dane
wrote:
Ok. Thank you all for your replies. It looks like I'll be copying the Formula
and pasting it into every cell in "F" as well, unless I'm misunderstanding
something. It does add the current "E" into a new total, but i still have to
manually paste the formula into each "F" cell to get the new total. At least
I dont have to do the math too.

"Lynz" wrote:
Gr8 Dane wrote:
I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
* * *"E" * * * * * * * * *"F"
Amount $ * * * * * * Balance
$5,000.00 * * * * * $5,000.00
-$0.04 * * * * * * *$4,999.96
$3.89 * * * $5,003.85
$24,000.00 * * *$29,003.85
$21,000.00 * * *$50,003.85
$16,000.00 * * *$66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

Try putting this in F2 =Sum($E$2:E2) and copy down. *Formula in F3 will
be =Sum($E$2:E3) etc. Is that what you wanted?
Hope that helps.

No you don't, just paste the code into the workbook module, then when
you place a new number in column E the total will change in Column F

I have columns E and F as being Currency. So it automatically adds a decimal
point and the last two characters as cents. (eg 123456 typed in, displays as
$1,234.56)
Other than that. I do not add any other characters besides numbers. I did
check that Calculation tab was set to add, but it did not change anything,
and everytime I go back to Options, it is back to default settings, as though
when I hit OK, it did not save my changes.

"Max" wrote:

It should work fine ..

a. Check that calc mode is not set to manual:
Click Tools Options Calculation tab
Check "Automatic" OK

b. If it isn't calc mode, then your data in col E probably contains text
nums, not real nums (or a mix). Convert the source data at one go to all real
nums like this. Copy any empty cell, select col E, right-click paste
special check "Add" ok. That should do it ok, and all should compute
properly now.

p/s: Remember to "high five" ALL responses which help, press the YES buttons
below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
"Gr8 Dane" wrote:
Sorry about the repeat Questoins. I got an error message saying that it did
NOT go through so I tried again. Apparently it really was going through after
all. Anyways, I tried your response, and it worked for the first cell. After
that I receieve that same answer every time. Is there a way for it to
automaticall change the new total?

Dave, I tried it. It isn't working. I type my new transaction into "E" and
"F" remains blank, unless I paste the formula into "F", and each "F". On a
side note, when I DO paste the formula in, it gives me the total, but the new
numbers have a much larger spacing to them. Is there also a way to get the
totals in "F" to match the size of the text (numbers) in "E"?

"CurlyDave" wrote:

On Feb 10, 2:59 am, Gr8 Dane
wrote:
Ok. Thank you all for your replies. It looks like I'll be copying the Formula
and pasting it into every cell in "F" as well, unless I'm misunderstanding
something. It does add the current "E" into a new total, but i still have to
manually paste the formula into each "F" cell to get the new total. At least
I dont have to do the math too.



No you don't, just paste the code into the workbook module, then when
you place a new number in column E the total will change in Column F

Gr8 Dane

Not sure I am following everything going on but it seems you have the
formula that works for you. It seem the problem now is that you need to copy
and paste it into each cell in column f to make your sheet work. If that is
the case, you need to drag your formula down as far as your data extends.
Try one of these: On the cell in column f that has your formula, move you
curser over the lower right corner, what you are looking for is the large
white cross to turn into a smaller black cross. This is called the "Fill
handle". Now depending on your layout you can try to double click and it
might fill you formulas to the bottom of your data, if it does not just click
on the fill handle and drag it to the bottom of your data.

I hope this helps

Mike Rogers
"Gr8 Dane" wrote:

I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

On Feb 10, 12:13*pm, Gr8 Dane
wrote:
I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
* * *"E" * * * * * * * * *"F"
Amount $ * * * * * * * Balance
$5,000.00 * * * * * * $5,000.00
-$0.04 * * * *$4,999.96
$3.89 * * * * $5,003.85
$24,000.00 * * *$29,003.85
$21,000.00 * * *$50,003.85
$16,000.00 * * *$66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

Talking of my personal experience, I have a workbook which I keep
track of my bank accounts. Maybe this might be interesting to you.
On each worksheet I create following procedure
A B C D
Date Deposit Withdraw Balance

Any money I deposit to my account, I type in deposit column and any
withdrawal on relevant column. In balance I type the following
formula: D2= fixed number which is the balance in the start of year
and D3=D2+B3-C3
And I copy down this formula (drag it down). On each raw, I enter
either on deposit or withdraw and not on both of them. Although it
will work fine on that way but I copy the structure of a bank note.
So I always have the balance of the account in the last cell of column
D.
I can repeat same procedure for different accounts in different sheets
and sum all of them in one sheet for summary.
This is exactly what you get in your bank notebook, where I make
exactly the same in my excel workbook.

Although in your case, you only use two columns, by adding one more
column, you can make life, easier?!?
Other benefits left unsaid like having the sum of deposit and withdraw
for each month.
Rather than replying to your question directly, I wanted to talk about
my experience which might give some hints to you.

On Feb 10, 3:32*am, Gr8 Dane
wrote:
Dave, I tried it. It isn't working. I type my new transaction into "E" and
"F" remains blank, unless I paste the formula into "F", and each "F". On a
side note, when I DO paste the formula in, it gives me the total, but the new
numbers have a much larger spacing to them. Is there also a way to get the
totals in "F" to match the size of the text (numbers) in "E"?

Interesting, did you paste the code in the WorkBook Module?

Everyone that is responding... Thank You... you all have helped. I am glad to
know about the "Fill Handle" Mike. Great Help. Ok, one more thing I
encountered now...
My worksheet will only go down to line/cell 1804. Cannot drag operating
window any lower. So I simply created a new sheet. However, On the second
sheet (since its really just the next page when printing out) I did not
include the top line of
the First sheet which inclucde "Date, Ref Type, Amount, etc). So, my
question/concern is this: When I copy and paste the formula into the second
sheet it starts all over from $0.00. How do I get it to continue on from the
first sheet which ended at $12,988,217.98? The only way I can get it to start
at this amount is to insert this amount into a new Line 1, but when I print
this out, this sheet 2 (starting at pg 37) will have this additional line in
it as well...


"Mike Rogers" wrote:


Gr8 Dane

Not sure I am following everything going on but it seems you have the
formula that works for you. It seem the problem now is that you need to copy
and paste it into each cell in column f to make your sheet work. If that is
the case, you need to drag your formula down as far as your data extends.
Try one of these: On the cell in column f that has your formula, move you
curser over the lower right corner, what you are looking for is the large
white cross to turn into a smaller black cross. This is called the "Fill
handle". Now depending on your layout you can try to double click and it
might fill you formulas to the bottom of your data, if it does not just click
on the fill handle and drag it to the bottom of your data.

I hope this helps

Mike Rogers
"Gr8 Dane" wrote:

I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

Nevermind this post! I inserted a new line, but excluded this line from the
operating window (white portion) so it does not affect any following lines,
nor page layouts, yet does give me a correct starting value. Thanks again
all. Much appreciated.

Gr8 Dane

"Gr8 Dane" wrote:

Everyone that is responding... Thank You... you all have helped. I am glad to
know about the "Fill Handle" Mike. Great Help. Ok, one more thing I
encountered now...
My worksheet will only go down to line/cell 1804. Cannot drag operating
window any lower. So I simply created a new sheet. However, On the second
sheet (since its really just the next page when printing out) I did not
include the top line of
the First sheet which inclucde "Date, Ref Type, Amount, etc). So, my
question/concern is this: When I copy and paste the formula into the second
sheet it starts all over from $0.00. How do I get it to continue on from the
first sheet which ended at $12,988,217.98? The only way I can get it to start
at this amount is to insert this amount into a new Line 1, but when I print
this out, this sheet 2 (starting at pg 37) will have this additional line in
it as well...


"Mike Rogers" wrote:


Gr8 Dane

Not sure I am following everything going on but it seems you have the
formula that works for you. It seem the problem now is that you need to copy
and paste it into each cell in column f to make your sheet work. If that is
the case, you need to drag your formula down as far as your data extends.
Try one of these: On the cell in column f that has your formula, move you
curser over the lower right corner, what you are looking for is the large
white cross to turn into a smaller black cross. This is called the "Fill
handle". Now depending on your layout you can try to double click and it
might fill you formulas to the bottom of your data, if it does not just click
on the fill handle and drag it to the bottom of your data.

I hope this helps

Mike Rogers
"Gr8 Dane" wrote:

I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.

BTW Mike, your reply was probably the most easy for me to follow, and did
EXACTLY what I was looking for... a mass copy without copy/paste every cell.
Thanks for great, and precise, advise.

"Mike Rogers" wrote:


Gr8 Dane

Not sure I am following everything going on but it seems you have the
formula that works for you. It seem the problem now is that you need to copy
and paste it into each cell in column f to make your sheet work. If that is
the case, you need to drag your formula down as far as your data extends.
Try one of these: On the cell in column f that has your formula, move you
curser over the lower right corner, what you are looking for is the large
white cross to turn into a smaller black cross. This is called the "Fill
handle". Now depending on your layout you can try to double click and it
might fill you formulas to the bottom of your data, if it does not just click
on the fill handle and drag it to the bottom of your data.

I hope this helps

Mike Rogers
"Gr8 Dane" wrote:

I have an open workbook with mulitiple sheets. I want to create a column "F"
that will keep track of all transactions made in column "E". e.g
"E" "F"
Amount $ Balance
$5,000.00 $5,000.00
-$0.04 $4,999.96
$3.89 $5,003.85
$24,000.00 $29,003.85
$21,000.00 $50,003.85
$16,000.00 $66,003.85

However, as of right now, I have to manually add/subtract "E" and then type
it in to "F". What can I do to have this done automatically? I am falling way
behind in doing this manually and need to catch up/remain current on 50+
pages, and counting.