expense sheet data into budget

March 11, 2013 at 05:08:58
Specs: Windows XP
have two drop down lists one for month and one for category. I need to have the amount entered placed in the corresponding cell in my budget as the items selected from the drop downs. The expense report and budget are in seperate worksheets within same workbook in excel. For example: If I select "Feb and supplies" I need it to be put into my budget under the Feb column in the supplies row. Thank YOU!!

See More: expense sheet data into budget

Report •


#1
March 11, 2013 at 09:49:21
Since we don't know the exact layout of your spreadsheet, the best we can offer is a concept. With more details, we can be more specific.

Let's say Sheet1 has a Monthly dropdown in A2, a Category dropdown in B2 and you are entering your values in C2:

Sheet1

      A          B         C          
1   Month    Category    Value
2    Feb    Supplies      250

Let's say Sheet2 looks something like this:

Sheet1

      A        B       C        D         
1   Month    Food   Supplies   Taxes
2    Jan
3    Feb              250
4    Mar    
...
13   Dec     

This Worksheet_Change macro will monitor Sheet1 for any change in C2. When it sees a change in Sheet1!C2 it will "find" the intersection on Sheet2 of the Month and Category shown in the Sheet1 drop downs and copy the value from Sheet1!C2 to that cell.

Let us know if we are on the right track.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Sheet1!C2
 If Target.Address = "$C$2" Then
'Find Month and Category on Sheet2
  With Sheets(2).Range("A1:D13")
'Find Month
   Set myRow = .Find(Sheets(1).Range("A2"))
'Find Category
   Set myCol = .Find(Sheets(1).Range("B2"))
  End With
'Use Row and Column values to Copy Sheet1!C2 to the
'intersection of Month and category
   Sheets(2).Cells(myRow.Row, myCol.Column) = Sheets(1).Range("C2")
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 11, 2013 at 12:15:18
I have an expense report in one worksheet that contains two drop down lists for the user to select the month the expense occurred and the type of expense: i.e. January, Birthday Cake
I need this data to go automatically into the corresponding cell on the budget. The budget is in the same workbook on a separate worksheet. The process is set up like so.

Amy’s expense report -> Amy’s Budget -> Departmental Budget

It is set up this way for all three people in the department. I have each individuals budget consolidated with the departmental budget so that the totals columns rolls over and is totaled in the Departmental Budget. In each individual budget I have left the cells open for the data from the expense report.

I can’t seem to make a formula work. I have totally confused myself. I’ve been teaching myself and I only have Excel 2003!

They look like this

Sheet 1

Month (drop down)

A B C D
Date details Amount Item (drop down)

Drop Down list in D matches list in A on sheet 2

Sheet 2

A B C
Jan Feb
Item1 ? ?
item2 ? ?


Report •

#3
March 11, 2013 at 12:48:15
First, a posting tip:

Please click on the blue line at the end of my posts and red the instructions found via that link. the instuctions will explain how to post data in the forum so that it retains its column alignment.

Once we can see your sheet layout, we may be able to offer more assistance. Based on what you've said so far, it appears that you are going to need a macro such as I suggested in my earlier response.

I don't think that a formula is going to work because a formula can't put data in any other cell than the cell the formula resides in. If the placement of the data is going to depend on multiple criteria (month, category, etc.) then I believe a macro will be needed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 11, 2013 at 13:51:17

They look like this

<b>Sheet 1</b>

       Month (drop down)

A      B            C        D
Date   details   Amount    Item(drop down)

Drop Down list in D matches 
list in A on sheet 2 and Month corresponds to 2nd row on sheet 2.


<b>Sheet 2</b>

A        B       C        D
        Jan     Feb     March
Item1    ?        ?       ? 
item2    ?        ?       ?

 



Thank you for your help and patience

Report •

#5
March 11, 2013 at 15:05:33
Well, I more or less gave you the solution in my first response. The only things that would need to be changed is the Range address where it looks for the Month and Item description and where it places the data. The concept is valid.

However, what you posted doesn't make sense it terms of an Excel spreadsheet. You didn't supply any Row numbers and it looks like your month drop down is above the Column letters.

Since the VBA code (the macro) has to refer to specific cells, I can't offer any more help unless I know the correct cells locations for your data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 18, 2013 at 04:37:34
Thank you so much. I was out of the office for a few days and am back to
tackle this again. I think this is what you need. THANK YOU!

    Sheet 1     
                  A      B          C                 D               E
row1
row2
row7                                 Month (drop down)
row8
row10
row11                  Date      Details         amount        item (drop down)

Month list is column C row 7and the items list is Column E row 11 thru 34. The page 
allows for multiple items to be listed.  

Sheet 2
A        B       C        D
        Jan     Feb     March
Item1    ?        ?       ? 
item2    ?        ?       ?

The first blank for the information to roll into is B 11. 
 It is the first item under January.  
The last cell that would recieve the data is
P102



Report •

#7
March 18, 2013 at 11:40:41
re:
"The first blank for the information to roll into is B11.
The last cell that would recieve the data is P102"

You didn't include Row numbers for Sheet2 so I'm still confused by your layout.

Question 1:

There are 12 months in a year. Your example data shows January in Column B (Sheet2!B10?) which leads me to surmise that December is in Column M.

If you are asking for the data to placed based on the Month shown in the Dropdown, how can the last cell be in Column P?

Question 2:

You seem to indicate that there are 24 items listed on Sheet1: "items list is Column E row 11 thru 34".

You state that the last cell to receive data is in P102. How can the last cell be in Row 102 if the first of 24 items is in Row 11? "The first blank for the information to roll into is B11."

In other words, I don't quite see how you are coming up with a range of B11:P102 to receive the data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
March 18, 2013 at 11:59:27
I am sorry I am so bad at communicating the information. You are being so patient. Thank you!

Answering your questions:
The columns that list the dates on sheet 2 contain additional columns for each quarter total . So it actually reads Jan, Mar, April, Q1, May.... So that it ends in column P.

When I said: "The first blank for the information to roll into is B11." I meant that is the first cell in Januarys column that coordinates with the first item. The items are listed in column A from A11 to A102.

On sheet 1 there are 24 blank spaces in which receipt information can be entered, and then the appropriate item selected from the drop down menu. The value of the receipt needs to then go into the appropriate cell on sheet 2 -as identified by the month chosen and the item chosen in the drop down menu in each of the 24 rows.


Report •

#9
March 18, 2013 at 12:23:09
The month drop down list is in C7, the Item drop down list is E11-34.
On sheet 1 there are 24 blank spaces in which receipt information can be entered, and then the appropriate item selected from the drop down menu. The value of the receipt needs to then go into the appropriate cell on sheet 2 -as identified by the month chosen and the item chosen in the drop down menu in each of the 24 rows. I need the info in D11 sent to worksheet 2 based on selection in C7month and E11item. For example: If I chose January and Brochures, then I need it to go into the budget in the column for January and the row for brochure.

Sheet 2
The items are listed on sheet 2 in column A from A11 to A102.
The months columns on sheet 2 contain additional columns for each quarter’s total. So it actually reads Jan, Mar, April, Q1, May.... So that it ends in column P.


Report •

#10
March 18, 2013 at 12:56:20
Here's the part where I am confused.

re:
Sheet 2
The items are listed on sheet 2 in column A from A11 to A102.

Are you saying that there are a total of 92 different items?

Does that mean that each of the dropdowns on sheet 1 contains 92 items?

I'm having trouble visualizing how the 24 drop downs from Sheet1 match up with the 92 items on Sheet2. It almost sounds like you could have the same item chosen more than once in the same month. If that's true I don't get how you plan to have that displayed on Sheet2.

What am I missing?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
March 18, 2013 at 14:07:13
DerbyDad03,

If I am confusing you so bad think of how bad I have it LOL. I am so sorry. Yes there are 92 different items. The same item could be selected more than once so the information on sheet 2 should acrue. Am I just an idiot for thinking this is possible?


Report •

#12
March 18, 2013 at 16:24:21
Well, I guess you could have the amounts sum each time they are chosen. That way the month would show the total amount spend on an item in that month. The code would simply add each 'new' receipt to the existing amount.

If that's what you want, that's fine, but that isn't what I thought you wanted, thus my confusion. When you said "the value of the receipt needs to go into the appropriate cell on Sheet 2, I envisioned one value per item per month being transferred.

If you want it summed, that's fine. If you don't then what do you want?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
March 20, 2013 at 15:01:08
YES! I do want it summed. Thank you so much for leading me through this. Would it help if you saw the actual workbook?

Report •

#14
March 20, 2013 at 17:20:26
I've sent you an email address via Private Message.

Please do not share the email address with anyone. Send the file to that address and I'll see what I can do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question