how to Data validation(list)

Excel Excel 2007 home and student
July 2, 2010 at 08:40:23
Specs: Windows XP
Hi
I've to column called "Type" and "value"
on the type I need combo list "Type 1, 2, 3"
on the value "val 1,2,3..."
when I select "type 1" Value list should contain only Val 1 & 2 then I will select one of the two,

is that possible to do this with data validation?
thanks


See More: how to Data validation(list)

Report •


#1
July 2, 2010 at 09:25:55
When you say you "need combo list Type 1, 2, 3" do you mean Type 1, Type 2 , Type 3?

The same question goes for Val 1, 2, 3.

Assuming that's the case, create your Data Validation list for your Types.

Create Named Ranges for the subsets of your Val list. For example, Val 1 and Val 2 could be named Type1. (You can't use spaces in a Name for a range, so we can't use Type 1. That means we have to play a little game later.)

OK, so let's say your Type validation list is in C1.

In D1, create a Validation List with this formula:

=INDIRECT(SUBSTITUTE(C1," ",""))

When you choose Type 1 in C1, SUBSTITUTE will "change" it to Type1 and the INDIRECT function will use that as the Named Range to populate the DropDown in D1.

Click below for more information on:

Dependent Validation Lists



Report •

#2
July 2, 2010 at 09:43:28
DerbyDad03

thanks for your fast reply, but I don't think you understand my need.
okay let me explain it better.

I've a validation on column C having a list of " Type1,Type2 &Type3" as you said. but no space b/b Type and the number.

and the same is true on the value too.

what I need is if I select Type1, on the second column(value) I only need to appear on the drop down list "Val1 & Val2" which is catagorized on type1(Type1 means can be val1 or val2)

Note that the validation source for both type and value can be anywhere in the spread sheet)

I don't know if if makes sense


Report •

#3
July 2, 2010 at 10:00:08
re: I don't think you understand my need.

Correct me if I am wrong, but it sounds like you want to create a Drop Down list that is dependent on another Drop Down list.

Depending on which of the "Types" is selected in the first Drop Down, the second Drop Down should only contain items that are associated with that specific "Type".

Before we go any further, tell me if that is right or wrong.


Report •

Related Solutions

#4
July 2, 2010 at 23:26:25
Hi DerbyDad03

yes that is what exactly I need to do.

thanks


Report •

#5
July 3, 2010 at 07:15:37
re: yes that is what exactly I need to do.

And that is exactly what I suggested in my first response.

Since I don't know your level of Excel expertise, I'll explain it in more detail. Nothing here is meant to be "degrading" but since I don't know what you know, I can't assume anything.

The "trick" behind Dependent Validation Lists is to use Named Ranges. A Named Range is a cell of range cells that been given a Name so that you can refer to it by that name instead of its cell references.

Example Using Named Ranges:

1 - Enter the number 1 through 10 in A1 through A10

2 - Select A1:A5.

3 - In the box above Column A, where it says A1, type in the word Type1 and hit Enter. Note: In order for the Name to be applied to that Range, you must hit Enter and not just click away from the box.

4 - Select A6:A10 and Name it Type2 using the instructions in Step 3 above.

5 - In B1 enter = SUM(A1:A5) and you should get 15.

6 - In B2 enter = SUM(Type1) and you should get 15 again because Excel will recognize Type1 as the Name of the Range A1:A5.

7 - In B3 enter =SUM(Type2) and you should get 40.

8 - In B4 enter =SUM(Type3) and you should get #NAME? because there is no Range named Type3.

The INDIRECT function

Excel provides an INDIRECT function so that you can refer to text in a cell and have Excel interpret it as a reference to a Range.

Using the Named Range example from above, try this:

9 - In C1 enter Type1

10 - In C2 enter Type2

10 - In C3 enter =SUM(C1) and you should get 0 since there is nothing in C1 for Excel to SUM.

11 - In C4 enter =SUM(INDIRECT(C1)) and you should get 15 because the INDIRECT function tells Excel that Type1 is a Range Name, which it can then SUM.

Dependent Validation Lists

OK, so back to the main issue.

You want to have a Validation List (VL) that is dependent on another VL. In order to do that, the Dependent VL (DVL) has to change the range of cells that it gets it data from depending on what has been selected in the Main VL.

Since we can't enter a list of Ranges for it to choose from, we need a method that automatically changes the range that it refers to.

To do that, we combine the use of Named Ranges and the INDIRECT function.

11 - In D1, create a VL using C1:C2 as the source so that you can select either Type1 or Type2.

12 - In E1, create a Vl using =INDIRECT(D1) as the source.

Note: If D1 is empty when you create the VL in E1, you'll get an error message. That's OK, just click Yes.

Now, when you select Type1 or Type2 from the drop down in D1, Excel will use that value in the INDIRECT function and recognize it as a Named range and populate the VL in E1 with the values from that range.

I hope this helps and if you have any more questions, come on back.

=======

P.S. Yhe only reason I used SUBSTITUTE in my first response is because you had spaces in your example (Type 1). Since we can't read minds, we can only assume that you meant what you posted. I had to deal with the space since Range Names can't contain spaces. What I was suggesting was that you name your ranges Type1 and Type2 and then deal with the space by replacing Type 1 with Type1 via the SUBSTITUTE function so that INDIRECT would recognize the Name.


Report •

#6
July 8, 2010 at 08:26:47
hi DerbyDad03

I appreciate your help but it doesn't work!! I've tried to implement what you write but this steps are not working

1 - Enter the number 1 through 10 in A1 through A10

2 - Select A1:A5.

3 - In the box above Column A, where it says A1, type in the word Type1 and hit Enter. Note: In order for the Name to be applied to that Range, you must hit Enter and not just click away from the box.

4 - Select A6:A10 and Name it Type2 using the instructions in Step 3 above.

5 - In B1 enter = SUM(A1:A5) and you should get 15.

Thanks anyways!
Temilla


Report •

#7
July 8, 2010 at 09:48:59
What's not working?

The steps you have included do nothing more than Name a couple of ranges and SUM 5 cells. In fact, the SUM formula doesn't even use the Named ranges created in steps 2 - 4.

What exactly does "not working" mean?


Report •


Ask Question