drop down list dependent on another drop down list.

[Guest]

pls can somone help me. I have posted on here previously but the advice
given didn't work. I know what I want doing can be done but I just don't
know how to do it! I also think I will have to email my spreadsheet to
someone as my explanation is not very straight forward.

So, in the spreadsheet I have two tabs, the main page, where I am creating
my formulas and data where I have all my “lists†created as named ranges.

So in the main page tab in cell I4 I have created a drop down list which
gives names of operations. This list is created from the “operations†named
range on the data sheet (cells A3:A20).

What I want to happen is, if for example I select Cholesystectomy from the
drop down list in I4 on the main page, I want cell J4 to offer me a drop down
list based on the named range of chole which is on the data sheet (T3:T5).
If I selected Nissen Fundoplication in from the drop down list in I4 then I
want cell J4 to offer me a drop down list based on the named range of nissen
on the data sheet (U3:U6), then if I selected Hellers in I4 I want J4 to give
me the drop down list of hellers and so on.

So in summary, I have about 15 things in the drop down list of "operations"
in cell I4, then for each of these 15 things I have an additional individual
drop down list of 3 or 4 items. I obviously need the drop down list in J4 to
be relevant to my first selection in I4.

Pls help, thank you in advance.

 

[Bob Phillips]

Have you looked at http://www.contextures.com/xlDataVal13.html#Depend

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

see:

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

 

[Guest]

First, check out http://www.xldynamic.com/source/xld.Dropdowns.html.
But I'll admit to a different approach... For just one-level of
dependencies, I'd create four columns. The first (say, column IS) is the
list of valid entries for the primary drop-down. The fourth (column IV).
Column two indicates, for each value in IS, the corresponding first row of
column IV where the valid list of secondary values begins. Column three
indicates, for each value in IS, the NUMBER of associated rows in column IV.
Ex:
Spreadsheet 1 2 Excel (there are two spreadsheet apps, starting in row 1)
Browser 3 3 1-2-3 (there are three browsers, starting in row 3)
Explorer
Firefox
Mozilla

My primary drop-down would be a data validation list based on column IS. In
some hidden cells, I'd have vlookups to calculate the starting row and row
count based on that primary drop-down. Finally, the secondary drop-down
would be a validation list that's calculated dynamically using the offset
function.
If I4 is the primary drop-down, then J4 might be =vlookup(I4,IS:IT,2,0) and
K4 =vlookup(I4,IS:IU,3,0). For the secondary drop-down, the validation list
would be =offset($IV$1,j4-1,0,k4,1).
--Bruce

 

[Guest]

See

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

Basically, if the names in drop down list in I4 match exactly the named
ranges i.e. 'Cholesystectomy' is the named range NOT 'Chole', then using Data
Validation in J4, select List and put "=INDIRECT(I4)" (no quotes) in Source.

HTH

 

[Guest]

Hi, thanks but none of this is helping. I have looked at the link before and
it doesn't address the problem.

the lists that I want to populate in my second cell ie the cell that is
dependent on the contents of the first cell are not called the same this is
because I have other sets of data later in the spreadsheet that need to be
dependent on that first cell.

 

[Debra Dalgleish]

You could set up a table that lists the ranges to use for each
selection, as described here:

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

 

[Guest]

Hi All, the CONTEXTURES link is not helping. Is there any other alternative?
Is there somewhere I can go where the formula is written for me as none of
these responses are working.

 

[Guest]

TRY:

Create a table with the I4 selections and corresponding range names. Name
this table e.g. "Selections"

Col A Col B
Cholesystectomy=>Chole
Nissen Fundoplication ==>Nissen

Using data Validation for J4, select LIST and set SOURCE to:

=indirect(VLOOKUP(I4,SELECTIONS,2,0))

HTH

 

[Guest]

Going to give it a go now. Thank you.

TRY:

Create a table with the I4 selections and corresponding range names. Name
this table e.g. "Selections"

Col A Col B
Cholesystectomy=>Chole
Nissen Fundoplication ==>Nissen

Using data Validation for J4, select LIST and set SOURCE to:

=indirect(VLOOKUP(I4,SELECTIONS,2,0))

HTH

 

[Guest]

TOPPERS, don't know who you are or where you are but a BIG THANK YOU. That
works.

 

[Guest]

Glad to have helped.

TOPPERS, don't know who you are or where you are but a BIG THANK YOU. That
works.

 

[Debra Dalgleish]

And that method is described at the link I provided earlier, that you
said wasn't working:

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

 

[Bob Phillips]

I think he wanted it served on a plate.

And that method is described at the link I provided earlier, that you said
wasn't working:

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

 

[Debra Dalgleish]

Well, to be fair, my example doesn't mention Nissen Fundoplication.