I want to enter a days sales then have it keep a running balance, then enter
over that days sales and have it add to the running balance.
b3 b4
$2 $2
then enter next day $2 over b3 have b4 be $4 and over and over for entire
month

Hi Dave,

I am not usre how is your spreadsheet designed.
Assuming that your balance is in cell A1 and daily sales in cell B1, enter
the below formula in cell A3, A4, A5... onwards

=B2+A2

Drag this formula till your desired range. I am assuming that row 1 is
headings row.
Hope this helps!!

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"dave" wrote:

I want to enter a days sales then have it keep a running balance, then enter
over that days sales and have it add to the running balance.
b3 b4
$2 $2
then enter next day $2 over b3 have b4 be $4 and over and over for entire
month

I'm not sure I follow how you plan to format your data, but keeping a
running balance is just a matter of a SUM formula that anchors on day
1 and includes a range up to the current day. Then you can copy that
formula into the next day and the range will shift to include that
next day.

Your post almost sounds like you want to overwrite daily data with
running balance data. If that's the case, then can I suggest NOT
overwriting source data, and instead using a different cell or column
for the running balance. This would allow the daily data to persist
unchanged in your spreadsheet. It's much easier to hide a column from
view than it is to have a figure with no idea how it was derived.

DaveO

i want to do this in two rows. Row b3 and b4 not a flowing sheet. I want to
enter daliy sales each day to b3 and have a running balance add to b4. tried
=sum b3:b4 did not work. If i sell $2 on October 1 I want b3 and b4 to say
$2. On october 2 I want to enter $3 daliy sales in b3 and have b4 say $5 and
I want to do that over and over every day of month. I would save each day
and rename each day and start fresh every first of month . Thanks
"Pranav Vaidya" wrote:

Hi Dave,

I am not usre how is your spreadsheet designed.
Assuming that your balance is in cell A1 and daily sales in cell B1, enter
the below formula in cell A3, A4, A5... onwards

=B2+A2

Drag this formula till your desired range. I am assuming that row 1 is
headings row.
Hope this helps!!

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"dave" wrote:

I want to enter a days sales then have it keep a running balance, then enter
over that days sales and have it add to the running balance.
b3 b4
$2 $2
then enter next day $2 over b3 have b4 be $4 and over and over for entire
month

You can't do this with a formula. You would need vba.

It isn't a good idea anyway. There is no record of history. If you happened
to catch the wrong key when entering the new data, you would have no way of
telling it had gone wrong, or knowing where or what to do. Have you never
added up a column of figures on a calculator and ended up with a ridiculous
total because of mis-keying?

If you really don't want to use one line per entry, don't bother with a
spreadsheet - just use a calculator.

Stephen

"dave" wrote in message
...
i want to do this in two rows. Row b3 and b4 not a flowing sheet. I want
to
enter daliy sales each day to b3 and have a running balance add to b4.
tried
=sum b3:b4 did not work. If i sell $2 on October 1 I want b3 and b4 to
say
$2. On october 2 I want to enter $3 daliy sales in b3 and have b4 say $5
and
I want to do that over and over every day of month. I would save each day
and rename each day and start fresh every first of month . Thanks
"Pranav Vaidya" wrote:

Hi Dave,

I am not usre how is your spreadsheet designed.
Assuming that your balance is in cell A1 and daily sales in cell B1,
enter
the below formula in cell A3, A4, A5... onwards

=B2+A2

Drag this formula till your desired range. I am assuming that row 1 is
headings row.
Hope this helps!!

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"dave" wrote:

I want to enter a days sales then have it keep a running balance, then
enter
over that days sales and have it add to the running balance.
b3 b4
$2 $2
then enter next day $2 over b3 have b4 be $4 and over and over for
entire
month

please read my reply to Pranav Vaidya to clear this up. It complicated but i
have a daily work sheet to balance my days biz with accounting codes etc and
it takes up a whloe sheet we do our extending and enter manually now and I
want to keep the same format. Thanks

"Dave O" wrote:

I'm not sure I follow how you plan to format your data, but keeping a
running balance is just a matter of a SUM formula that anchors on day
1 and includes a range up to the current day. Then you can copy that
formula into the next day and the range will shift to include that
next day.

Your post almost sounds like you want to overwrite daily data with
running balance data. If that's the case, then can I suggest NOT
overwriting source data, and instead using a different cell or column
for the running balance. This would allow the daily data to persist
unchanged in your spreadsheet. It's much easier to hide a column from
view than it is to have a figure with no idea how it was derived.

DaveO

Dave

Are you sure you want to do this?

Think about it after reading the following.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in ToolsOptionsCalculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and EditEnter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Also see John McGimpsey's site for VBA method and the same caveats as above.

http://www.mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP

On Wed, 17 Oct 2007 08:11:06 -0700, dave wrote:

please read my reply to Pranav Vaidya to clear this up. It complicated but i
have a daily work sheet to balance my days biz with accounting codes etc and
it takes up a whloe sheet we do our extending and enter manually now and I
want to keep the same format. Thanks

"Dave O" wrote:

I'm not sure I follow how you plan to format your data, but keeping a
running balance is just a matter of a SUM formula that anchors on day
1 and includes a range up to the current day. Then you can copy that
formula into the next day and the range will shift to include that
next day.

Your post almost sounds like you want to overwrite daily data with
running balance data. If that's the case, then can I suggest NOT
overwriting source data, and instead using a different cell or column
for the running balance. This would allow the daily data to persist
unchanged in your spreadsheet. It's much easier to hide a column from
view than it is to have a figure with no idea how it was derived.

DaveO

Hi Dave
Please take good note of all the warnings given about using a single cell
accumulator.

The following code will give the "appearance" of what you want, whilst
maintaining an audit trail of all values entered.
Using a change event code on the worksheet, will add the value entered in
Cells B4:B34 in B3.
It will then hide row 4, fill in the next date in A5 and B5 will be the next
row to enter data.
Each time you enter a value, the same procedure will occur, so you are
always entering you new data immediately below cell B3, but it's actual
location will keep changing.

Format cell A3 FormatCellsNumberCustommmmm
Enter in cell B3 = SUM(B4:B34)
At the end of the month, mark rows 3:35Right clickUnhide.
Mark cells A4:B35Delete and this will clear down all of the data for the
month. Enter a new Month date in cell A3 and start all over again.

At any time, you can unhide the rows and view all of the data that has been
entered. When you have finished looking, just hide rows 4 through to the
last row where data has been entered in column B.

Copy the code belowright click on your sheet tabView codePaste the code
into the white pane.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lrow As Long
lrow = Cells(Rows.Count, "B").End(xlUp).Row
If Target.Count 1 Then Exit Sub
If Target.Row < 4 Then Exit Sub
If Target.Column < 2 Then Exit Sub

If Target.Value = 0 Then

Select Case MsgBox _
("Did you mean to enter a value of Zero?", _
vbYesNo Or vbQuestion Or vbDefaultButton1, _
"Zero Value Entered")

Case vbYes

Case vbNo
Target.Value = ""
Cells(lrow + 1, 2).Select
Exit Sub

End Select

End If
Application.ScreenUpdating = False
Application.EnableEvents = False

Cells(lrow, 1) = Format(Cells(3, 1) + lrow - 4, "dd-mmm")
Cells(lrow + 1, 1) = Format(Cells(3, 1) + lrow - 3, "dd-mmm")
Cells(lrow, 1).EntireRow.Hidden = True
Cells(lrow + 1, 2).Select
endsub:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

--
Regards
Roger Govier



"dave" wrote in message
...
i want to do this in two rows. Row b3 and b4 not a flowing sheet. I want
to
enter daliy sales each day to b3 and have a running balance add to b4.
tried
=sum b3:b4 did not work. If i sell $2 on October 1 I want b3 and b4 to
say
$2. On october 2 I want to enter $3 daliy sales in b3 and have b4 say $5
and
I want to do that over and over every day of month. I would save each day
and rename each day and start fresh every first of month . Thanks
"Pranav Vaidya" wrote:

Hi Dave,

I am not usre how is your spreadsheet designed.
Assuming that your balance is in cell A1 and daily sales in cell B1,
enter
the below formula in cell A3, A4, A5... onwards

=B2+A2

Drag this formula till your desired range. I am assuming that row 1 is
headings row.
Hope this helps!!

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"dave" wrote:

I want to enter a days sales then have it keep a running balance, then
enter
over that days sales and have it add to the running balance.
b3 b4
$2 $2
then enter next day $2 over b3 have b4 be $4 and over and over for
entire
month