Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Populate fields based upon dropdown selection
I'm building a simple planning template, but I want it to be
multi-functional. Here's what I'd like it to do: INPUT SHEET: User is prompted to select task from a dropdown list. List has three options [OPT1, OPT2, OPT3] When user selects a particular option, a column on that INPUT SHEET is automatically populated with list of tasks that need to be done for that option. Option 1 has 10 tasks. Option 2 has 5 tasks. Option 3 has 20 tasks. Each task list is in a separate sheet (TASK SHEET) within the workbook that will be hidden for users. How do I make this happen - and can I make this happen with different sized task lists? For reference, I would want the Options data to begin in Cell A5 of the Input Sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Populate fields based upon dropdown selection
Check out Debra's site on creating dynamic validation:
http://www.contextures.com/xldataval02.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Del" wrote: I'm building a simple planning template, but I want it to be multi-functional. Here's what I'd like it to do: INPUT SHEET: User is prompted to select task from a dropdown list. List has three options [OPT1, OPT2, OPT3] When user selects a particular option, a column on that INPUT SHEET is automatically populated with list of tasks that need to be done for that option. Option 1 has 10 tasks. Option 2 has 5 tasks. Option 3 has 20 tasks. Each task list is in a separate sheet (TASK SHEET) within the workbook that will be hidden for users. How do I make this happen - and can I make this happen with different sized task lists? For reference, I would want the Options data to begin in Cell A5 of the Input Sheet. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Populate fields based upon dropdown selection
Okay, let's pretend I didn't really understand most of what was on that page...
In my B1 Cell, the user selects Option1, Option2, or Option3. Upon doing so, this should trigger and auto-population of the cells in column A, starting with A5 and going down as long as the Option list is. After reading that website, I have no idea where I'm supposed to put any formulas. In my mind, it's a simple "If I choose X, then Y happens." I just don't have the knowledge to make it happen. That's where I need the help. "Luke M" wrote: Check out Debra's site on creating dynamic validation: http://www.contextures.com/xldataval02.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Del" wrote: I'm building a simple planning template, but I want it to be multi-functional. Here's what I'd like it to do: INPUT SHEET: User is prompted to select task from a dropdown list. List has three options [OPT1, OPT2, OPT3] When user selects a particular option, a column on that INPUT SHEET is automatically populated with list of tasks that need to be done for that option. Option 1 has 10 tasks. Option 2 has 5 tasks. Option 3 has 20 tasks. Each task list is in a separate sheet (TASK SHEET) within the workbook that will be hidden for users. How do I make this happen - and can I make this happen with different sized task lists? For reference, I would want the Options data to begin in Cell A5 of the Input Sheet. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Populate fields based upon dropdown selection
Ah, slightly different than what I origianlly thought. Lets go with this...
The important part is your data setup. Let's assume you have a seperate worksheet (I'll call it DATA) setup as your data table. Row 1 contains your different option possiblities, with the list of sub-obtions that you want to appear underneath them. Option 1.....Option 2......Option 3 Op1_A..........Op2A..........Op3A Op1_B.............................Op3B (apologies if pseudo-table gets distorted) "Option 1" is located in cell A1, as a frame of reference. Select all the cells in Row 1 that contain info, and give this a name (creating a name range), let's call it MyRange. You can do this using Insert - Name - Define. Now, in your sheet where you want everything to happen, you can setup data validation on cell B1. Under Data - Validation, Custom, input formula: =MyRange Cell B1 is now setup to display your options as the only possible choices. Now, to create the autolist. In A5: =IF(OR($B$1="",OFFSET(DATA!$A$1,ROW($A1),MATCH($B$ 1,DATA!$1:$1,0)-1)=""),"",OFFSET(DATA!$A$1,ROW($A1),MATCH($B$1,DAT A!$1:$1,0)-1)) Copy this down as far as needed to cover your largest option list. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Del" wrote: Okay, let's pretend I didn't really understand most of what was on that page... In my B1 Cell, the user selects Option1, Option2, or Option3. Upon doing so, this should trigger and auto-population of the cells in column A, starting with A5 and going down as long as the Option list is. After reading that website, I have no idea where I'm supposed to put any formulas. In my mind, it's a simple "If I choose X, then Y happens." I just don't have the knowledge to make it happen. That's where I need the help. "Luke M" wrote: Check out Debra's site on creating dynamic validation: http://www.contextures.com/xldataval02.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Del" wrote: I'm building a simple planning template, but I want it to be multi-functional. Here's what I'd like it to do: INPUT SHEET: User is prompted to select task from a dropdown list. List has three options [OPT1, OPT2, OPT3] When user selects a particular option, a column on that INPUT SHEET is automatically populated with list of tasks that need to be done for that option. Option 1 has 10 tasks. Option 2 has 5 tasks. Option 3 has 20 tasks. Each task list is in a separate sheet (TASK SHEET) within the workbook that will be hidden for users. How do I make this happen - and can I make this happen with different sized task lists? For reference, I would want the Options data to begin in Cell A5 of the Input Sheet. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto Populate fields based upon dropdown selection
YES! That just made my day! :) I don't even grasp the formula, but it works
exactly like I need it to. Thanks!!!! "Luke M" wrote: Ah, slightly different than what I origianlly thought. Lets go with this... The important part is your data setup. Let's assume you have a seperate worksheet (I'll call it DATA) setup as your data table. Row 1 contains your different option possiblities, with the list of sub-obtions that you want to appear underneath them. Option 1.....Option 2......Option 3 Op1_A..........Op2A..........Op3A Op1_B.............................Op3B (apologies if pseudo-table gets distorted) "Option 1" is located in cell A1, as a frame of reference. Select all the cells in Row 1 that contain info, and give this a name (creating a name range), let's call it MyRange. You can do this using Insert - Name - Define. Now, in your sheet where you want everything to happen, you can setup data validation on cell B1. Under Data - Validation, Custom, input formula: =MyRange Cell B1 is now setup to display your options as the only possible choices. Now, to create the autolist. In A5: =IF(OR($B$1="",OFFSET(DATA!$A$1,ROW($A1),MATCH($B$ 1,DATA!$1:$1,0)-1)=""),"",OFFSET(DATA!$A$1,ROW($A1),MATCH($B$1,DAT A!$1:$1,0)-1)) Copy this down as far as needed to cover your largest option list. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Del" wrote: Okay, let's pretend I didn't really understand most of what was on that page... In my B1 Cell, the user selects Option1, Option2, or Option3. Upon doing so, this should trigger and auto-population of the cells in column A, starting with A5 and going down as long as the Option list is. After reading that website, I have no idea where I'm supposed to put any formulas. In my mind, it's a simple "If I choose X, then Y happens." I just don't have the knowledge to make it happen. That's where I need the help. "Luke M" wrote: Check out Debra's site on creating dynamic validation: http://www.contextures.com/xldataval02.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Del" wrote: I'm building a simple planning template, but I want it to be multi-functional. Here's what I'd like it to do: INPUT SHEET: User is prompted to select task from a dropdown list. List has three options [OPT1, OPT2, OPT3] When user selects a particular option, a column on that INPUT SHEET is automatically populated with list of tasks that need to be done for that option. Option 1 has 10 tasks. Option 2 has 5 tasks. Option 3 has 20 tasks. Each task list is in a separate sheet (TASK SHEET) within the workbook that will be hidden for users. How do I make this happen - and can I make this happen with different sized task lists? For reference, I would want the Options data to begin in Cell A5 of the Input Sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Populate Based on Other Selection | Excel Discussion (Misc queries) | |||
How to Auto-populate cell based on selection of a list item | Excel Discussion (Misc queries) | |||
Auto populate other fields? | Excel Discussion (Misc queries) | |||
Auto populate several cells based on a selection from drop down li | Excel Discussion (Misc queries) | |||
Auto populate fields | Excel Discussion (Misc queries) |