How do I add this kind of functionality to a drop down list?

[bovinda]

So I just figured out how to make a drop down list and am now very proud of myself.

But I have no clue what-so-ever how to add this kind of functionality. It's for my spreadsheet that I use to keep track of my finances...

Here's what I would like to do: Selecting an option from a drop-down list causes the value entered in one cell to also appear in another cell (that is predetermined by the selection in the drop down list).

Example: I enter 12 into cell A3. In B3 is a drop down list containing "Food," "Rent," and "School" as options. If I select "Food" then whatever value is in A3 (in this case, the value "12") is also copied into C3. If I select "Rent," then A3's value is copied into D3 instead, or to E3 if I select "School."

Is this possible at all? I've heard that Visual Basic can be used to enable more functionality to things like drop-down lists, but don't know if that's true, or how I would go about doing that. I don't know the first thing about scripting or programming of any sort, and am in fact probably slightly mentally defunct when it comes to such things.

I hope this is clear (and if not, I'll clarify!). If you have an alternative suggestion I would also love to hear your ideas!

And any suggestions as to how I could do this or where to go to find out about this are immensely, immensely appreciated!

 

[jindon]

Hi

As I don't know the row of headings, I set it to 1, so change it if needed.
Try the code anyway, and if you find any inconvenience, let me know.
1) right click on the questioned sheet tab and select View Code
2) paste the code there and close the window
3) change the value of the cell that yoou want to activate the code.

note:
As I utilised Filter Function to test if the value is DebitItem or not.
x = Filter(DebitItem, Cells(TR, "e"), False, vbBinaryCompare)
This function is faster than loop through the array each time, but
if you have "ATM" is in the dropdwonlist and "ATM Withdrawal" in
DebitItem, this function reacts as it is IN the DebitItem.
Therefore, please change it to like "ATM Saving" or something like that.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DebitItem, TR As Long, x, HeadRow
HeadRow = 1     '< < set heading row here
If Target.Count > 1 Then Exit Sub
If Target.Row <= HeadRow Then Exit Sub
'** when you need to change item(s), change or add to array below ***
DebitItem = Array("ATM Withdrawal", "Debit", "Check", "CC Pay 1", "CC Pay 2", "CC Pay 3")
TR = Target.Row '** activecell(target) row number
Application.EnableEvents = False
Select Case Target.Column
    Case 4, 5   '** if the change made to col. D or E
        ' *** if both D and E has value ***
        If Application.CountA(Cells(TR, "d"), Cells(TR, "e")) = 2 Then
            ' ** test if E value is in DebitItem or not
            x = Filter(DebitItem, Cells(TR, "e"), False, vbBinaryCompare)
            If UBound(x) <> UBound(DebitItem) Then '* if is in DebitItem
                Cells(TR, "h") = Cells(TR, "d") * -1 ' negative D value in H
            Else
                Cells(TR, "h") = Empty  ' if not, H is empty
            End If
        Else
            Cells(TR, "h") = Empty  ' if either of D or E has value, H is empty
        End If
        With Range("d65536").End(xlUp).Offset(, 3)   ' last row of G
            .Offset(1).Formula = "=sumif(H:H,""=""&"""",D:D)" 'next row of last row
            .Offset(2) = Empty
            If IsNumeric(.Value) Then .Value = ""
        End With
    Case 7  ' ** if change made to col G
        If Target.Value = "Eat In" Then
            Cells(TR, "b").Font.Color = vbRed
        ElseIf Target.Value = "Eat Out" Then
            Cells(TR, "b").Font.Color = vbBlue
        Else
            Cells(TR, "b").Font.ColorIndex = 0
        End If
End Select
        If IsEmpty(Cells(TR, "g")) Then     ' ** if col.G is empty
            If Cells(TR, "e") = "Debit" Then
                Cells(TR, "b").Font.ColorIndex = 29
            ElseIf Cells(TR, "e") = "Check" Then
                Cells(TR, "b").Font.Color = vbGreen
            Else
                Cells(TR, "b").Font.ColorIndex = 0
            End If
        End If
Application.EnableEvents = True
End Sub

 

[bovinda]

jindon, thank you so much! I will give it a try as soon as I get home tonight. What is the head row or row of headings? Is that like the title of a row? Anyway, thank you again! I'll let you know how it works out tonight.

 

[mwb]

For your drop downs, you can also use 'Data', Validation. You can create a list of selections or link it to a worksheet area (where the list is). Your if statement will work the same and you will not need to make objects on your worksheet. The data validation can be copy/pasted so it is very easy to expand your spreadsheet.

 

[bovinda]

Thanks mwb, I will definitely do that. So objects can't be cut and pasted as easily, huh? Is there any advantage to using objects over the data validation method then?

 

[jindon]

bovinda,

Yes, it's a tittle row.

 

[bovinda]

Hi jindon, thank you for the great work you put into the code! It works exactly how I wanted it to, with one exception. I didn't have any row titles by the way, so I think whatever you used must have been fine (is there an advantage to row titles?).

When I enter a numerical value into D39, for example, like "25," it does indeed total it up into the G column. However, when I enter "Debit" into E39, it does not include the value in the total (although it does put the negative value into H39, which is awesome!). How could I adjust the code so that some entries in column E (like "Debit" and "Check" for example) will also allow the numerical values of the D column to be included in the total in column G?

Anyway, I'm going to play around with it some this weekend and see what I can do with it. Thank you so much for your help man!

 

[jindon]

Hi,

What is in DebitItem array is not included i the total.

I need to see what are exactly inclusive and exclusive items,
because you didn't show what they exactly are.

rgds,
jindon

 

[bovinda]

Hi jindon, I got it. Sorry, I wasn't very clear. Here is my full drop-down list (what I will actually end up using) for all the rows in column E, along with whether or not (1) they would cause D's numerical value to be included in G's total and (2) whether or not they would cause D's numerical value to be listed negatively in column H or positively in column I.

E's Category -- D's value Included in G's total? -- D's value negative in H or positive in I?
Cash -- Inc in G total -- Not in H
Debit -- Inc in G total -- Negative in H
Check -- Inc in G total -- Negative in H
Credit Card, WF -- Inc in G total -- Not in H
Credit Card, AI -- Inc in G total -- Not in H
Credit Card, Exp -- Inc in G total -- Not in H
Credit Card, VS -- Inc in G total -- Not in H
Credit Card, Blank -- Inc in G total -- Not in H
ATM Withdrawal -- Not in G total -- Negative in H
CC Pay WF -- Not in G total -- Negative in H
CC Pay AI -- Not in G total -- Negative in H
CC Pay Exp -- Not in G total -- Negative in H
CC Pay VS -- Not in G total -- Negative in H
CC Pay Blank -- Not in G total -- Negative in H
Dep Fi-Aid, Jeff -- Not in G total -- Positive in column I
Dep Fi-Aid, Pam -- Not in G total -- Positive in column I
Dep, Other -- Not in G total -- Positive in column I
Work, Jeff -- Not in G total -- Positive in column I
Work, Pam -- Not in G total -- Positive in column I


Obviously you don't have to input all those. If I can figure out how you do a couple of them, I will do the rest if it's a lot of trouble.

After that I have one last question. I was wondering if it's possible to make separate totals in column G, for example: say G41 would have the totals for rows D39-D40; G44 would have the totals for D42-D43; G47 would have the totals for D45-D46, and so on. Is that a possibility with the code? Does that get incredibly complex?

The reason I ask is because keep running totals for each day. (For example, D39-40 has expenses from January 1, D42-43 has expenses from January 2, and so on. I insert rows if I need more space to list expenses, and each expense goes as a separate entry in each row in column B. Just in case you were curious! )

jindon, I want you to know I don't expect you to endlessly write code for me at my whim. I really appreciate what you've done so far. At any point, feel free to be like "I've done what I can. Figure some of this out!" and I'll do my best with what I've got, then maybe post back in a couple weeks. You've really helped me out, and I am extremely grateful for your time. Thanks man! If at any point you want a copy of the spreadsheet I'm working on by the way, just to see what it's looking like, let me know and I'll send you a copy. I'm really proud of it, silly as it may be!

Cheers, Jeff

 

[jindon]

Hi bovinda,

OK, but I need to go out now.
I'll come back to you about 4 hours later.

rgds,
jindon

 

[jindon]

Jeff,

Where is your date column?

Regarding separate sub total.
Isn't it better to create a kind of matrix table in other columns or even in other sheet?
Because as you add the data, it goes way down the list.

Let's just make sure that your dropdown items are categorised
1) Includes only G total
2) Includes G total and Negative in H
3) Excludes G total and Negative in H
4) Excludes G total and Positive in I

rgds,
jindon