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.

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.

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.

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.

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.