Tie a budget with my check register

[jlbarkley]

I have budget worksheet that I use and it works very well. I am trying to have a check register on another tab for that month with drop down categories that will automatically change the values in my budget. For instance if I budget $300 for groceries and I go to the store I want to put into my register that I spent x amount of dollars on my register tab and if I choose groceries in the drop down menu then it would automatically add that to the budget tab. I am sure this is possible, but I just simply don't have that skill. Thanks so much in advance.

 

[Alice Morland]

Hi there. Could you post screenshots of what you have? It seems that you have a budget in one tab and an expenses list in another, correct? And you'd like that the budget tab to include a sheet which updates automatically and which derives from the expense tab? That should be simple, but I'm confused about the drop down menu requirement...

 

[jlbarkley]

I apologize, I can't figure out how to put an image in this. Either way I think I can explain easily. So I have two tabs: "May Budget 2018" and the other is "May Register 2018"

In the budget we will just use one line item. I have from left to right "Item>Estimated Cost>Actual Cost>Difference. So for this example it is Groceries>$300>$0.00>$300. So I have budgeted 300 for groceries these two weeks and I haven't spent any money on groceries yet.

In the second I want to be able to have the cells from left to right be Date>Description>Posted>Item>Amount>Total

I know how to do simple SUM to make the amount and total populate automatically. The thing I don't know how to do is if I put the word groceries OR have a drop down within the cell and choose groceries where it will automatically add to my "actual cost" on the first tab specifically for groceries. This was I can sort by date like a normal check register than make separate columns for specific items I have. Thanks so much. I will figure out a screen shot if I didn't make my explanation clear.<attachment></attachment>

 

[mumps]

Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[jlbarkley]

https://1drv.ms/x/s!AtJwiv_skzZmjU-99QKmmT6IB_h_

This is the rough draft of what I have. Please let me know if this doesn't work, but it should I shared it through Microsoft Onedrive. So essentially in the second tab on the worksheet I put one line with Groceries. Above that section it's called "Budget Assignment". I want to make it where when I put groceries, for example, in that section it automatically will add it to my budget in the first tab. So if I have budgeted $300 for groceries and in my check register I spend $100 with the budget assignment of groceries then my budget will now automatically reflect I only have 200 left. I am hoping this makes sense. I don't want to use an online software. Would much rather use my own worksheet so I can edit it and make it better as time goes on.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "May Register 2018" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter an item under BUDGET ASSIGNMENT and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lTotals As Long
    lTotals = Range("A:A").Find("Totals").Row
    Dim foundVal As Range
    If Intersect(Target, Range("A7:A" & lTotals - 1)) Is Nothing Then Exit Sub
    Set foundVal = Sheets("May Budget '18").Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        foundVal.Offset(0, 2) = Target.Offset(0, 4)
    End If
End Sub

 

[jlbarkley]

That code almost works. It's making the budget cell that it's tied to blank rather than putting a number in it.

 

[jlbarkley]

That code almost works. It's making the budget cell that it's tied to blank rather than putting a number in it. I can definitely tell it's trying to do something to the exact cell I need it to, but it's not making the currency go into it. It's just making it blank instead.

 

[mumps]

The macro worked properly on the sample file you posted. Click here to download your file. Enter the word "Groceries" in cell A7 of the "May Register 2018" sheet and exit the cell. You will see that the value of $100.00 from cell E7 has been entered in cell D39 of the "May Budget '18" sheet. If you are using the macro on a different file, it may not work properly if there is even a small difference in how the data is organized. If you are still having problems and are using the macro on a different file then upload a copy of the file that is not working for you.