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:
- Click Formulas tab
- In the Defined Names group, click Name Manager
- Click New
- In the Name box, type Expenses
- In the Refers to box, type the following =OFFSET($A$2,0,0,COUNTA($A$2:$A$50),1)
- Click OK
Step 2: Setup your Input sheet’s lists; on your second sheet:
- Select cells A2 through A50
- Click Data tab
- In the Data Tools group, click Data Validation
- In the Allow drop down box, select List
- In the Source box, type the following =Expenses
- 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:
- Click Developer tab
- If the Developer tab is not present Click the Office Button
- Click Excel Options
- Check off Show Developer tab in Ribbon
- In the Controls group, Click Insert
- Select the Button (Form Control)
- Draw your button on your sheet and the Assign Macro dialog will appear
- Click New
- Now copy and paste the following code between Sub Button<number>_Click() and End Sub</number>
Code:
' -----------------------------------------------------------------------------------------
' 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!