How to create custom expense worksheet with daily subtotal WITHOUT visual basic

[kmanbob]

Dear Excel experts,


Is it possible to do this WITHOUT Visual Basic or programming or even without a table, because I want it to be as simple as possible, but I might settle for having a table


column A is for date, column B is for daily subtotal (I want it to appear next to the date), column C is for individual amounts of expenses, column D is just a description of the expense


example :


A1 = 12/25/2013
B1 = (I want the Subtotal (or sum of the entire day's expenses to automatically appear here next to the date, after a new date, (12/26/2013) has been entered in column A)) in this case, it would sum C1:C3 which is $6... or up until the next date that is inputed in column A)
C1 = $1
D1 = cake
C2 = $2
D2 = juice
C3 = $3
D3 = milk
A4 = 12/26/2013
B4 = (again, same thing as B1)
etc.


Hopefully, I've adequately explained what I would like to achieve


TIA for any leads & solutions

 

[L. Howard]

How did $1 for cake, $2 for juice and $3 for milk get into the cells C & D.

And when you enter a new date, what happens with the existing $1 for cake, $2 for juice and $3 for milk?

Howard

 

[kmanbob]

The prices and description of the expense in C & D are inputted by me, as is the date in A

Once I input another date in A, nothing happens to C or D,

C & D are just so I can input expenses as the day goes on etc....

What I am trying to do is have an automatic subtotal in B (one instance next to each date inputed in A)... it would be ideal if the subtotal continues to be dynamic as I keep adding expenses for that date in C, but once I input a new date in A, the subtotal will stop calculating for the previous date... and a new subtotal will appear next to the new date in A in B.

 

[L. Howard]

Well, =SUM(C1:C10) in B1 will total up to 10 daily expenses listed in column C. Increase if needed.

But if you change the date in A1 nothing changes the B1 total until you manually delete all the daily expenses in C column and start anew with expenses for the new date.
And of course you would need to manually delete the items in D column too.

Is that what you are shooting for.

Know that a formula can ONLY return a value (or string) to the cell it is in, it can't 'do stuff' to other cells. So no formula in B1 is going to change anything except what is returned to B1.

You said no VBA so unless you go with a worksheet change event that will fire whenever you change the contents of A1, for example code that clears C and D columns entries when change occurs in A1, then you are left to do it your self manually.

For your info the code would look something like this and only fires if a change is made to A1, where upon it clears range C1:D10.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("C1:D10").ClearContents
End Sub

Howard

 

[kmanbob]

I understand how to manually do a SUM. The point is I want it to be automatic because one day I may only have 3 items, while another day I may have 20. And I don't need anything to be cleared. The daily sum would appear in column B next to every instance I input a date in column A. So my spreadsheet would be growing longer as I input every new date.

I would attach a simple spreadsheet here, if only I knew how

Well, =SUM(C1:C10) in B1 will total up to 10 daily expenses listed in column C. Increase if needed.

But if you change the date in A1 nothing changes the B1 total until you manually delete all the daily expenses in C column and start anew with expenses for the new date.
And of course you would need to manually delete the items in D column too.

Is that what you are shooting for.

Know that a formula can ONLY return a value (or string) to the cell it is in, it can't 'do stuff' to other cells. So no formula in B1 is going to change anything except what is returned to B1.

You said no VBA so unless you go with a worksheet change event that will fire whenever you change the contents of A1, for example code that clears C and D columns entries when change occurs in A1, then you are left to do it your self manually.

For your info the code would look something like this and only fires if a change is made to A1, where upon it clears range C1:D10.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("C1:D10").ClearContents
End Sub

Howard

 

[kmanbob]

Here is the solution I settled with. The point is that I didn't want to deal with VB, Pivot Tables, or array functions. It's not very sophisticated, but it'll do.

http://www.excelforum.com/excel-for...btotals-without-vb.html?p=3239415#post3239415