Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 14th 09, 07:14 PM posted to microsoft.public.excel.misc
del del is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 16
Default 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   Report Post  
Old September 14th 09, 07:26 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2007
Posts: 2,722
Default 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   Report Post  
Old September 14th 09, 07:54 PM posted to microsoft.public.excel.misc
del del is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 16
Default 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   Report Post  
Old September 14th 09, 09:11 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2007
Posts: 2,722
Default 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   Report Post  
Old September 14th 09, 09:43 PM posted to microsoft.public.excel.misc
del del is offline
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 16
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Populate Based on Other Selection Kate Excel Discussion (Misc queries) 2 May 15th 07 10:57 PM
How to Auto-populate cell based on selection of a list item AK9955 Excel Discussion (Misc queries) 2 April 30th 07 10:04 AM
Auto populate other fields? sedonovan Excel Discussion (Misc queries) 3 June 21st 06 05:34 PM
Auto populate several cells based on a selection from drop down li Sheldon Excel Discussion (Misc queries) 3 January 13th 06 08:12 PM
Auto populate fields Mark Excel Discussion (Misc queries) 1 September 15th 05 08:45 PM


All times are GMT +1. The time now is 08:33 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017