How to use a drop down list to add values to a different sheet?

[cyberdimitri]

I am trying to create a personal budget management spreadsheet.
Ideally it would be like this:
On the first worksheet I would make a table, on column A I would list the various expenses (for example rent, electricity, transport etc) and on line 1 the months of the year, leaving A1 empty.
On the second worksheet I would like to be able to:
On cell A1 to select the day of the week (MON to SUN) from a drop down list.
On cell B1 to select the date of the month (1 to 31) from a drop down list.
On cell C1 to select the month (JAN to DEC) from a drop down list.
On cell D1 to select the year (2012 to 2099) from a drop down list.
On cells A2 to Axx to select the name of the expense from a drop down list that I would have created earlier.
On cells B2 to Bxx to put the amount of the specific expense.
Then after pressing a button (named "FINISH" or "ENTER" or something) the values from column B should update the corresponding line and column of worksheet one, while clearing everything on worksheet two so i could later put new data for the next day.
I am a noob and I would appreciate any help, especially if it didn't involve difficult macros or if it did, they were sufficiently explained.
Thanks.

 

[Rosen]

For argument sake I am going to assume you’re using Excel 2007 since you didn’t specify. While this can be accomplished in any version of excel post 97 (or at the very latest from 2002 onward) the instructions are geared towards the 2007 layout. Also I am assuming that 50 expense items will be sufficient; if that is not the case, change any 50 you see in blue to the number which is appropriate. (Note: Blank rows between data in Column A could break this system, do not leave blank rows between data in either column A)

Step 1: Creating a Dynamic Named Range for your expenses, while on your first sheet:

1. Click Formulas tab
2. In the Defined Names group, click Name Manager
3. Click New
4. In the Name box, type Expenses
5. In the Refers to box, type the following =OFFSET($A$2,0,0,COUNTA($A$2:$A$50),1)
6. Click OK

Step 2: Setup your Input sheet’s lists; on your second sheet:

1. Select cells A2 through A50
2. Click Data tab
3. In the Data Tools group, click Data Validation
4. In the Allow drop down box, select List
5. In the Source box, type the following =Expenses
6. Click OK

These instructions work for each dropdown list you wish to create on the second sheet, only subsection (1) and (5) will need to be modified as follows:

A1 = MON,TUE,WED,THUR,FRI,SAT,SUN
B1 = 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...
C1 = JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG...
D1 = 2012,2013,2014,2015,2016,2017...


* please note that the ... does not mean that Excel should know what comes next or know when to stop, only that there is more but I am too lazy to type it out.

Step 3: Adding the button; Unfortunately I am not aware of any way to do what you want without VBA, and I am a really bad judge of difficulty level, so I will try and provide sufficient explanation in the comments of the code regardless of its complexity.But first, creating the button:

1. Click Developer tab
   1. If the Developer tab is not present Click the Office Button
   2. Click Excel Options
   3. Check off Show Developer tab in Ribbon
2. In the Controls group, Click Insert
3. Select the Button (Form Control)
4. Draw your button on your sheet and the Assign Macro dialog will appear
5. Click New
6. Now copy and paste the following code between Sub Button<number>_Click() and End Sub</number>

    ' -----------------------------------------------------------------------------------------
    ' Declairing Variables
    ' -----------------------------------------------------------------------------------------
    Dim SourceSheetName As String
    Dim DestinationSheetName As String
    Dim InputDate As String
    Dim lColumn As Long, lRow As Long, lSourceRow As Long
    
    ' -----------------------------------------------------------------------------------------
    ' I am assuming that the sheets that information was just inputted to is Sheet2 and that
    ' where the data is going is Sheet1, if this is not the case, change the names below.
    ' -----------------------------------------------------------------------------------------
    SourceSheetName = "Sheet2"
    DestinationSheetName = "Sheet1"
    
    ' -----------------------------------------------------------------------------------------
    ' Putting our date entered into our second sheet together, note that there is no validation
    ' of this date, if it is not entered it will still run and add your information.
    ' -----------------------------------------------------------------------------------------
    With Sheets(SourceSheetName)
        InputDate = .Range("A1").Value & " " & .Range("C1").Value & " " & .Range("B1").Value _
                    & ", " & .Range("D1").Value
    End With
    
    ' -----------------------------------------------------------------------------------------
    ' We need to find/create our column for data. It is assumed that if a column with our date
    ' already exist we are updating/adding values, we have not built anything to delete values.
    ' -----------------------------------------------------------------------------------------
    lColumn = 2
    Do Until Sheets(DestinationSheetName).Cells(1, lColumn).Value = vbNullString
        
        ' -------------------------------------------------------------------------------------
        ' If we find a Column which already has the date, we are finished looking for our
        ' column
        ' -------------------------------------------------------------------------------------
        If Sheets(DestinationSheetName).Cells(1, lColumn).Value = InputDate Then Exit Do
        lColumn = lColumn + 1
    Loop
    
    ' -----------------------------------------------------------------------------------------
    ' We are going to loop through each row (from 2 to the first blank row) in our source sheet
    ' and add the data in column B to our corresponding Column in the destination sheet.
    ' -----------------------------------------------------------------------------------------
    lSourceRow = 2
    Do Until Sheets(SourceSheetName).Range("A" & lSourceRow).Value = vbNullString
        
        ' -------------------------------------------------------------------------------------
        ' We have data to input, so now we need to know where it goes.  Look through our
        ' destination worksheet to find the associated row.
        ' -------------------------------------------------------------------------------------
        lRow = 2
        Do Until Sheets(DestinationSheetName).Range("A" & lRow).Value = vbNullString
            If Sheets(DestinationSheetName).Range("A" & lRow).Value = _
               Sheets(SourceSheetName).Range("A" & lSourceRow).Value Then Exit Do
            lRow = lRow + 1
        Loop
        Sheets(DestinationSheetName).Cells(lRow, lColumn).Value = _
        Sheets(SourceSheetName).Range("B" & lSourceRow).Value
        lSourceRow = lSourceRow + 1
    Loop
    
    ' -----------------------------------------------------------------------------------------
    ' Cleaning up our workspace.
    ' -----------------------------------------------------------------------------------------
    With Sheets(SourceSheetName)
        .Range("A1:D1").ClearContents
        Do Until .Range("A" & lRow).Value = vbNullString
            .Range("A" & lRow & ":B" & lRow).ClearConents
        Loop
    End With

I hope this helps!