#1   Report Post  
Old December 21st 05, 07:01 PM posted to microsoft.public.excel.misc
Annabelle
 
Posts: n/a
Default Dropdown Lists

I have Software Review Report that I'd like to add dropdown lists from
a second worksheet, but unclear on how to execute. Can you assist?

1. I would like Review Type, Defect Type and Defect Cause to be drop
downs populated by the second worksheet.

The second worksheet has 5 different review types; each Review Type has
about 10 Defect Types; each Defect type has about 20 Defect Causes.
This is an example of what the second worksheet looks like:

Review Type Defect Type Defect Cause
RT1....................DT1....................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2
...............................................DC3
...............................................DC4
RT2....................DT1..................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2


2. The Review Type will determine which Defect Type and Defect Causes
are populated in their respective drop down.


  #2   Report Post  
Old December 21st 05, 07:11 PM posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Dropdown Lists

See http://www.xldynamic.com/source/xld.Dropdowns.html

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Annabelle" wrote in message
ups.com...
I have Software Review Report that I'd like to add dropdown lists from
a second worksheet, but unclear on how to execute. Can you assist?

1. I would like Review Type, Defect Type and Defect Cause to be drop
downs populated by the second worksheet.

The second worksheet has 5 different review types; each Review Type has
about 10 Defect Types; each Defect type has about 20 Defect Causes.
This is an example of what the second worksheet looks like:

Review Type Defect Type Defect Cause
RT1....................DT1....................DC1
..............................................DC2
..............................................DC3
.........................DT2..................DC1
..............................................DC2
.........................DT3..................DC1
..............................................DC2
..............................................DC3
..............................................DC4
RT2....................DT1..................DC1
..............................................DC2
..............................................DC3
.........................DT2..................DC1
..............................................DC2
.........................DT3..................DC1
..............................................DC2


2. The Review Type will determine which Defect Type and Defect Causes
are populated in their respective drop down.



  #3   Report Post  
Old December 21st 05, 07:16 PM posted to microsoft.public.excel.misc
Jacob_F_Roecker
 
Posts: n/a
Default Dropdown Lists

The only way I can think of to do this is with some formulas based upon
if_then statements that would be hidden on the worksheet and make up the drop
down list.

I'd like to have your spreadsheet to see if my idea will work before I try
to overcomplicate things by explaning an idea that might not work.

Would you feel comfortable sending me the sheet?



Thanks for the challenge

"Annabelle" wrote:

I have Software Review Report that I'd like to add dropdown lists from
a second worksheet, but unclear on how to execute. Can you assist?

1. I would like Review Type, Defect Type and Defect Cause to be drop
downs populated by the second worksheet.

The second worksheet has 5 different review types; each Review Type has
about 10 Defect Types; each Defect type has about 20 Defect Causes.
This is an example of what the second worksheet looks like:

Review Type Defect Type Defect Cause
RT1....................DT1....................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2
...............................................DC3
...............................................DC4
RT2....................DT1..................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2


2. The Review Type will determine which Defect Type and Defect Causes
are populated in their respective drop down.


  #4   Report Post  
Old December 21st 05, 09:20 PM posted to microsoft.public.excel.misc
Therese
 
Posts: n/a
Default Dropdown Lists

....If you get..please share!! :-)
--
Therese


"Jacob_F_Roecker" skrev:

The only way I can think of to do this is with some formulas based upon
if_then statements that would be hidden on the worksheet and make up the drop
down list.

I'd like to have your spreadsheet to see if my idea will work before I try
to overcomplicate things by explaning an idea that might not work.

Would you feel comfortable sending me the sheet?



Thanks for the challenge

"Annabelle" wrote:

I have Software Review Report that I'd like to add dropdown lists from
a second worksheet, but unclear on how to execute. Can you assist?

1. I would like Review Type, Defect Type and Defect Cause to be drop
downs populated by the second worksheet.

The second worksheet has 5 different review types; each Review Type has
about 10 Defect Types; each Defect type has about 20 Defect Causes.
This is an example of what the second worksheet looks like:

Review Type Defect Type Defect Cause
RT1....................DT1....................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2
...............................................DC3
...............................................DC4
RT2....................DT1..................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2


2. The Review Type will determine which Defect Type and Defect Causes
are populated in their respective drop down.


  #5   Report Post  
Old December 21st 05, 09:35 PM posted to microsoft.public.excel.misc
Annabelle
 
Posts: n/a
Default Dropdown Lists

I just sent you the spreadsheet. Thanks for taking a look.



  #6   Report Post  
Old December 28th 05, 02:42 AM posted to microsoft.public.excel.misc
Jacob_F_Roecker
 
Posts: n/a
Default Dropdown Lists

Dear All:

There's a better way than the way I did it. If you've got your inbox set up
to recieve this reply you're going to like this.

http://www.contextures.com/xlDataVal02.html

OK.

There's one 'flaw' with the example and that is what if you have two columns
of dependent information. Take a look at Annabelle's example below. RT1
Determines TWO fields of information not just one.

The solution I found was to create an intermediate cell. The example will
show you selecting fruit or vegtables and then a list of each. But what if
you had selected:

Category Type Grows on
Fruit Apple Trees

I made the a cell then you'd have two categories dependent upon the first
selection. I created a sell that sees the Category and generates another
name for the "Grows On" column. For example fruit becomes fruit one in the
intermediate cell. Then the "grows on" column goes to the list for fruit1
and displays that in the dropdown window.

The formula looks something like this:

=IF('sheet'!B4=D3,"Fruit1",IF('Review '!B4=D4,"Vegetable1",IF('sheet1
'!B4=D5,"Minerals1",IF('sheet1 '!B4=D6,"Animals1"," "))))


Of course I wouldn't actually create a list with minerals on it but I think
it gets the idea across.

KUDOS to RagDyer for solving the original problem so efficiently!

-Jacob F. Roecker


"Therese" wrote:

...If you get..please share!! :-)
--
Therese


"Jacob_F_Roecker" skrev:

The only way I can think of to do this is with some formulas based upon
if_then statements that would be hidden on the worksheet and make up the drop
down list.

I'd like to have your spreadsheet to see if my idea will work before I try
to overcomplicate things by explaning an idea that might not work.

Would you feel comfortable sending me the sheet?



Thanks for the challenge

"Annabelle" wrote:

I have Software Review Report that I'd like to add dropdown lists from
a second worksheet, but unclear on how to execute. Can you assist?

1. I would like Review Type, Defect Type and Defect Cause to be drop
downs populated by the second worksheet.

The second worksheet has 5 different review types; each Review Type has
about 10 Defect Types; each Defect type has about 20 Defect Causes.
This is an example of what the second worksheet looks like:

Review Type Defect Type Defect Cause
RT1....................DT1....................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2
...............................................DC3
...............................................DC4
RT2....................DT1..................DC1
...............................................DC2
...............................................DC3
..........................DT2..................DC1
...............................................DC2
..........................DT3..................DC1
...............................................DC2


2. The Review Type will determine which Defect Type and Defect Causes
are populated in their respective drop down.




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
Help Please with Dropdown lists and Vlookup TotallyConfused Excel Discussion (Misc queries) 2 December 1st 05 09:07 PM
Do dropdown lists have an autocomplete function? thg Excel Discussion (Misc queries) 2 August 7th 05 10:14 PM
continuing dropdown lists BLW Excel Worksheet Functions 1 May 16th 05 08:55 PM
Dropdown lists metrueblood Excel Discussion (Misc queries) 1 February 10th 05 12:17 AM
how do I add data validation dropdown lists to a Form SteveD.IFlora Excel Worksheet Functions 3 January 21st 05 04:48 PM


All times are GMT +1. The time now is 11:45 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017