How do you tell excel to add a value from one cell to another when something specific occurs?

Question

I am creating a budget and general ledger and I need the dollar amount I enter to add to a cell that is specified by the GL Code.

When the "GL Code" for that line is labelled "Council Food" or the number I will represent it with on the disbursement form, I want the dollar amount for that entry (22.50) to add to the cell I've selected on the Master Budget sheet. I want to be able to do this for all of the different lines in the Master Budget.

What are the columns on the "Master Budget" for? They look like they might have dates going across the top, but it's cut off above row 17 in the screenshot so I'm not sure... — elmer007, Jan 14 '17 at 00:28

elmer007 · Accepted Answer · 2017-01-14T00:29:58.927

0

I think the SUMIFS function might help you toward this. For example:

=SUMIFS('Disbursement Ledger'!$B:$B,'Disbursement Ledger'!$F:$F,"Council Food")

will sum everything in column B from the "Disbursement Ledger" sheet where column F has the text "Council Food".

However, you should probably add a column to your "Master Budget" sheet that contains the GL Codes for each line. Let's say you put the GL Codes in column H (so, H36 would be "Council Food"), then your formula for G36 would be:

=SUMIFS('Disbursement Ledger'!$B:$B,'Disbursement Ledger'!$F:$F,$H36)

So, if your next entry in the disbursement ledger was for $10.00 and had a GL Code of "Council Food", then your G36 cell on the "Master Budget" sheet would then show $32.50.

edited Jan 14 '17 at 00:29

answered Jan 14 '17 at 00:24

elmer007

1,412
14
27

This literally did everything for me. Thank you so much! – PotatoSalad Jan 14 '17 at 04:52
@PotatoSalad Glad to help – elmer007 Jan 14 '17 at 14:00

score 0 · Answer 2 · edited May 23 '17 at 11:45

You have to use worksheet event. In the vent handler add if condition to process specific row and column. Refer link and some precautions.

W.r.t. processing logic @elmer007 has mentioned about SUMIF. It should not be much problem to proceed further.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
'Here range depends on the cells you want to track
Set KeyCells = Range("A1:C10")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    ' Place your processing code here.       
End If
End Sub

How do you tell excel to add a value from one cell to another when something specific occurs?

2 Answers2