Drop Down Boxes

[Guest]

Anyone can help with this?

Drop - Down Box - Finance, Accouting, etc. (5 others)

Expenses:
Relocation, Recruiting, etc. (list has 30 items)
Now, I am forecasting so they will put these expenses for 12 months for each
expense for each department. Therefore, if I create multiple drop down lists,
I still have the same problem, because it is the same expense types for each,
however, the amounts will all vary depending on department. I can not create
multiple lists because it is too much stuff. Any ideas on how I can do this?
Thanks.

 

[Guest]

A.S.,

This is a little unclear, at least to me. First, is this on a form or on a
worksheet. Maybe a small example might help me or someone else to better
understand what you're trying to do.

Art

 

[Guest]

DEPARTMENTS (drop-down with Fin, acct,
etc.)
M1 M2 M3 M4 M5 M6 M7 M8 M9 M10
M11 M12
Exp 1
Exp 2
etc.

So basically...and data would be put in for each month for each expense.
However, the departments cell is a drop-down menu, so they can choose
whichever department. So I would like it so that if Finance is chosen and the
user puts in the amount $100 for M1, Exp 1. Then they can then go back to the
drop-down and choose Accounting and re-enter an amount for M1, Exp 1, which
would ony be for accounting. Hope this explanation helps.

 

[Guest]

A.S.,

So it sounds like you will need another place to store the results once
they're entered.

The drop downs should be able to be handled pretty easilty with data
validation. Also, it seems like you might not want drop downs for the
expense categories as your example has them coded to each line.

Assuming that for right now, you would need a macro behind the sheet to
recognize that data has been entered. Let's assume you have a bunch of
hidden sheets, one for each department. When data is entered, you can
capture that, check the value of the department drop down, and store the
entry in the proper departmental sheet.

You may have simplified your example, and perhaps I'm missing something
important, but I think this might be a sensible way to start.

Art

 

[Guest]

Hi Art,
Yeah I think that we on the same page now. So basically I need it to hold
onto the information, so how would I do this? What macro can be used so that
once the data is input for a category, it is stored, and a differenct
category can be chosen then to input data? Thanks for the help

 

[Guest]

Okay, for an example I did the following:

On sheet "Entry"
Cell E1 has the department drop down.
B22 has M1 M2 M3
A3 has Exp1
A4 has Exp2

There is also a sheet Dept One, and Dept Two -- these are the values in the
drop down.

The following macro is in the "Entry" sheet (you get there from the macro
editor and look for the sheet name):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mRow As Long
Dim mCol As Integer

'Find out where the data went
mRow = Target.Row
mCol = Target.Column

'ignore anything outside of the input region
If mRow > 20 Then Exit Sub
If mCol > 4 Then Exit Sub

'Put the data in the other sheet.
Select Case Sheets("Entry").Range("E1")
Case "Dept One"
Sheets("Dept One").Cells(mRow, mCol) =
Sheets("Entry").Cells(mRow, mCol)
Case "Dept Two"
Sheets("Dept Two").Cells(mRow, mCol) =
Sheets("Entry").Cells(mRow, mCol)
End Select
End Sub

You may want to copy that to something that has a longer line width so it's
readable.

I may not be able to continue on this today -- but if you need more help
later today, perhaps someone else can jump in.

Art