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!

 

[bovinda]

Hey jindon, no rush at all with this thing, only at your convenience.

My date column is column A. I have separate sheets for each month of the year, and a box that keeps running totals of all the different kinds of expenditures at the top of each sheet for all the months. So the "Jan" sheet for January will have 1-31 in column A, each separated by about three or four rows to allow for expenditure entries. I've been doing it that way to allow me to help me see what things are my biggest expenditure each day. Column G has the separate subtotals for each day, with a total for the month at the top of the column. What do you think of that layout?

The four ways you categorized how the D values are handled by the dropdown lists sounds perfect. I wouldn't even have thought to categorize them that way. That makes a lot of sense.

Thanks jindon,
Jeff

 

[jindon]

OK Jeff, I'll do so.

I'll reply when my head is really clear.

rgds,
jindon

 

[jindon]

Hi
I used col.Z as working column. data invisible
in the sheet module

Private Const HeaderRow As Integer = 1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TR As Long, x, flag As Boolean
Dim IncG_NegaH, ExcG_PosiI, ExcG_NegaH
If Target.Count > 1 Then Exit Sub
If Target.Row <= HeadRow Then Exit Sub
IncG_NegaH = Array("Debit", "Check")
ExcG_PosiI = Array("Dep Fi-Aid. Jeff", "Dep Fi-Aid, Pam", "Dep, Other", "Work, Jeff", "Work, Pam")
ExcG_NegaH = Array("ATM Withdrawal", "CC Pay WF", "CC Pay AI", "CC Pay Exp", "CC Pay VS", "CC Pay Blank")
TR = Target.Row: flag = False
Application.EnableEvents = False
Select Case Target.Column
    Case 4, 5
        If Application.CountA(Cells(TR, "d"), Cells(TR, "e")) = 2 Then
            x = Filter(IncG_NegaH, Cells(TR, "e"), False, vbBinaryCompare)
            If UBound(x) <> UBound(IncG_NegaH) Then
                Cells(TR, "h") = Cells(TR, "d") * -1
            Else
                Cells(TR, "h") = Empty
                x = Filter(ExcG_PosiI, Cells(TR, "e"), False, vbBinaryCompare)
                If UBound(x) <> UBound(ExcG_PosiI) Then
                    Cells(TR, "i") = Cells(TR, "d"): flag = True
                Else
                    Cells(TR, "i") = Empty
                    x = Filter(ExcG_NegaH, Cells(TR, "e"), False, vbBinaryCompare)
                    If UBound(x) <> UBound(ExcG_NegaH) Then
                        Cells(TR, "h") = Cells(TR, "d") * -1: flag = True
                    End If
                End If
            End If
        End If
        If flag = True Then
            Cells(TR, "z") = "n": flag = False
        Else
            Cells(TR, "z") = Empty
        End If
    Case 7
        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 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
Sheets("sheet1").subtotal
Application.EnableEvents = True
End Sub

Sub subtotal()
Dim r As Range, dic As Object, x, y, i, Lastr
Set dic = CreateObject("Scripting.Dictionary")
Application.EnableEvents = False
Lastr = Range("e65536").End(xlUp).Row
Range("z" & HeaderRow & ":z" & Lastr).NumberFormat = ";;;"
Range("a" & Lastr + 1 & ":i" & "65536").ClearContents
With Range("g" & Lastr).Offset(1)
    .FormulaR1C1 = "=sumif(r" & HeaderRow & "c26:r" & Lastr & "c26,""<>""&""n"",r" & HeaderRow & "c4:r[-1]c4)"
End With
For Each r In Range("a1:a" & Lastr)
    If Not IsEmpty(r.Value) And IsDate(r.Value) And _
        Not dic.exists(r.Value) Then
        dic.Add r.Value, Nothing
    End If
Next
x = dic.keys: Set dic = Nothing: ii = 4
For i = LBound(x) To UBound(x)
    With Range("a" & Lastr + ii)
        .Value = x(i)
        .Offset(, 6).FormulaR1C1 = _
        "=sumproduct(--(r" & HeaderRow & "c1:r" & Lastr & "c1=rc1)*--(r" & HeaderRow & "c26:r" & Lastr & "c26<>""n""),r1c4:r" & Lastr & "c4)"
    End With
    ii = ii + 4
Next
Application.EnableEvents = True
End Sub

 

[bovinda]

Hey jindon, sorry for the late response. Thank you so much for the work! I haven't had a chance to test it out yet--these last few days have been a little hectic with my work schedule. But as soon as I can I will let you know how it works out. I don't think I could even begin to decipher what you wrote! What do you do for work, does it relate to this? How did you learn this? Anyway, I will talk to you soon,

Jeff

 

[UHsoccer]

I have already submitted a separate thread : Autofilter display subtotals. I read this thread and I would have 50 categories (one for each state). Not to look at multiple filters such as State, city, etc.

Would that make this kind of code not unwieldy?


Showing subtotals when using filters should be on many peoples wish list, not?

 

[bovinda]

Hey jindon,

The last couple of days I have been fooling around with my spreadsheet trying to get the arrangement right of the cells, and playing with the formulas. I learned a lot about writing some of the formulas. But in the process, I think I also rearranged some of the columns in such a way that it mixed the code up because it changed where the cells had originally been referred to. On one hand, I think I found a way to do everything I wanted to with simple formulas, with two exceptions that I just found today in testing it out, and I thought I would seek out your help one last time. Your formula suggestions were invaluable in me getting to this point, by the way!

Basically, the last two things I need help with are:
(1) When I insert a row, it doesn't copy all the formulas from the preceding row. For example, I have "IF" formulas in columns T through AX that refer to earlier cells specific to each row. Inserting a row does not paste the "IF" formulas. Is there some simple way to accomplish this?

(2) I also still want to be able to change the font color of cells in columns B and C depending on entries in columns E or F. I'm having trouble isolating the part of the code you wrote that does this, though I know it's in there because it worked spectacularly before. It's probably not as simple as just cutting that portion of the code out and keeping it, is it?

Anyway, thanks for the work man! You've really educated me about a lot of this stuff, especially the formulas, and I really appreciate the work you put into the code. I wish I had something to offer back. If you ever need advice from a first year medical student, seek me out!

Cheers,
Jeff