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.

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Annabelle" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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?

[email protected]

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.
>
>

....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?
>
> [email protected]
>
> 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.
> >
> >

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

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?
> >
> > [email protected]
> >
> > 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.
> > >
> > >