Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down Boxes
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down Boxes
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 "A.S." wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down Boxes
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. "Art" wrote: 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 "A.S." wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down Boxes
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 "A.S." wrote: 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. "Art" wrote: 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 "A.S." wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down Boxes
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 "Art" wrote: 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 "A.S." wrote: 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. "Art" wrote: 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 "A.S." wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drop Down Boxes
Okay, for an example I did the following:
On sheet "Entry" Cell E1 has the department drop down. B2:D2 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 "A.S." wrote: 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 "Art" wrote: 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 "A.S." wrote: 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. "Art" wrote: 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 "A.S." wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down Boxes | New Users to Excel | |||
Drop down boxes/tick boxes | Excel Worksheet Functions | |||
Drop Down Boxes | Charts and Charting in Excel | |||
Drop down boxes.. | Excel Discussion (Misc queries) | |||
Drop down boxes | Excel Worksheet Functions |