Data Validation List - Dependant based on numeric values

[Roady]

Hi:

I have two columns as follows:
Dept Brand
2 Hanes, Leggs
8 Converse, Aerosoles
12 Levi's, Dockers

When the user selects the dept # in col A, I want it to restrict the
drop-down in Col B to only show the applicable brands. Problem is I can't use
the typical Indirect formula method because it won't allow me to name the
cell with a number.

Can you offer an alternative? Thanks!

 

[KC hotmail com>]

You simply need to follow the additional step shown here.
http://www.contextures.com/xlDataVal02.html#Illegal

You can call the revised lists Two, Eight, Twelve, etc. since using the
actual numbers is not a viable option.

 

[Roger Govier]

Hi Roady

Take a look at the tutorial I wrote on Debra Dalgleish's site
http://www.contextures.com/xlDataVal15.html

This will allow you to use Numbers in your master list, and then as column
headers for the lists of available products

--
Regards
Roger Govier

Hi:

I have two columns as follows:
Dept Brand
2 Hanes, Leggs
8 Converse, Aerosoles
12 Levi's, Dockers

When the user selects the dept # in col A, I want it to restrict the
drop-down in Col B to only show the applicable brands. Problem is I can't
use
the typical Indirect formula method because it won't allow me to name the
cell with a number.

Can you offer an alternative? Thanks!

__________ Information from ESET Smart Security, version of virus
signature database 4849 (20100208) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4849 (20100208) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Roady]

Thanks, guys. I tried this but it didn't work. Does the data in the "data
entry" tab and the "list" tab need to be on the same corresponding beginning
row/cell? I tried to convert and substitute using my table locations but it
would result in an error. Because of the nature of the document, I am unable
to use the exact same cell references used in the example.

Data entry tab:
1st dropdown starts in C442 and its dependant drop down in D442.

Lists tab:
Table starts in B236 (header starts in B235)

any thoughts on what I might be missing in the conversion? Thanks!

 

[KC hotmail com>]

The location of your source lists for the data validation is
irrelevant...you're referencing a named list that can be located pretty much
anywhere.

You'd ideally create your lists on another sheet (let's say Sheet2 for the
sake of simplicity...I realize you mention a Tables sheet starting in row
235, but since I'm not sure what all is around that data, we'll use a clean
Sheet2 for our example).

So in Sheet2!A2 you'd begin with your first department #, then in Sheet2!B2
you'd type out that number in words (so A2 would show 2, B2 would show Two).
Finish out that table, then highlight A2:A100 (or however long that table
goes) and call it DeptList, then highlight A2:B100 and call it DeptLookup.

Then in row 1 will be those spelled out numeric names (so D1 would be Two,
E1 would be Eight, etc.) and underneath each one type out your lists (so D2
might be Hanes, D3 might be Leggs, etc...then E2 might be Converse, E3
Aerosoles, and so on).

Next, take D23 (or however long that list is) and name it Two, then take
E2:E3 and name it Eight, and so on.

Finally, DataEntry!C442 will have Data Validation > List with Source
=DeptList and DataEntry!D442 will have Data Validation > List with Source
=INDIRECT(VLOOKUP(C442,DeptLookup,2,0))