Create conditional drop-down lists

re: Create conditional drop-down lists

guatdoc — Fri, 11 Feb 2011 17:47:54 GMT

is there a way to have the drop list with items, yet allow a user to input text if there is nothing valid in the list?

re: Create conditional drop-down lists

KAS — Thu, 24 Dec 2009 04:05:05 GMT

indirect work well with drop down. however,it is not working with combo box. any one can help me how to use indirect in combo box

re: Create conditional drop-down lists

Tow — Mon, 14 Dec 2009 23:58:20 GMT

I can get this to work great without using an underscore or period, but entering anything other than a single value and the drop-down box fails to open??

Any Help?

re: Create conditional drop-down lists

TONG SeowPin — Mon, 14 Dec 2009 00:14:27 GMT

Add the following EVENT to your workbook, when you change your second option (2nd data validation option), the 1st option will dissapear.

eg. when you selected the wrong city 1st time, you

re-select again, the country will appear blank immediately. That means you need to re-select again the correct country based on the re-selected city.

+++++++++++++++++++++++++++++++++++++++++++

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

''www.mrexcel.com/.../showthread.php

Application.EnableEvents = True

If Not Intersect(Target, Range("Q2")) Is Nothing Then

Application.EnableEvents = False

Range("Q3").Value = ""

Application.EnableEvents = True

End If

End Sub

+++++++++++++++++++++++++++++++

Any doubts refer to the mrexcel for more information & solution.

rgds,

re: Create conditional drop-down lists

Ovas — Sat, 12 Dec 2009 04:53:02 GMT

Dear jirina42

Can u show the table and one formula somehow?

re: Create conditional drop-down lists

Samir — Wed, 09 Dec 2009 08:30:35 GMT

Hi!

I can not proceed following:

1. Select cell D2.

2. On the ribbon, click the Data tab.

3. In the Data Tools group, click Data Validation.

4. Fill the values as follows:

In the Allow box, select List.

In the Source box, type =INDIRECT($C2)

Unknown valus but not question if I want continue.

Any idea why?

Sincearely Samir-SWEDEN

re: Create conditional drop-down lists

jirina42 — Fri, 04 Dec 2009 20:41:23 GMT

Hi there,

this can be actually done totally dynamically. I mean that it's not necessary to create named ranges for every set of values so that you can freely add new values and they are automatically considered in the validation. It can be also combined with an array function retrieving distinct values from a 'raw' list so that you can feed your validation list from basically any source of data.

The validation formula is then of course a bit more complicated but it works perfectly.

I'd send a sample file but I don't know where.

Below is how the validation works but without the excel it's not very understandable.

jirina42

Validations:

Practice

=CCsHeader

"=OFFSET(CCsData;0;MATCH(I26;CCsHeader;0)-1;

COUNTA(OFFSET(CCsData;0;MATCH(I26;CCsHeader;0)-1;

14;1));1)"

"What does it do:

- takes value in cell I26 (the practice for which we are creating list of CCs) and finds it's position in the CCsHeader (the first MATCH function)

- creates a range of the CCs under the selected practice (Microsoft in our case) (the outter OFFSET function)

- the inner COUNTA function (and everything inside) counts number of CCs under the selected practice. It's pretty the same as the stuff before that but we need to limit the area where to count the values (max. 14 values in our case but it can also be e.g. whole column)."

Name

"=OFFSET(NamesData;0;MATCH(J26;NamesHeader;0)-1;

COUNTA(OFFSET(NamesData;0;MATCH(J26;NamesHeader;0)-1;

13;1));1)"

"What does it do:

Exactly the same thing as the CC validation except that it looks for range of Names under the CC selected in cell J26"

re: Create conditional drop-down lists

Matt — Thu, 03 Dec 2009 14:33:25 GMT

Can you provide a link to the example so that we can see it in action.

Thanks

re: Create conditional drop-down lists

taiwo — Mon, 30 Nov 2009 15:25:35 GMT

Its nice being a member of this group. Because I found it so interesting learning on line. especially on Microsoft excel.

thanks regards

re: Create conditional drop-down lists

TopDad99 — Thu, 26 Nov 2009 14:45:25 GMT

I'm not a fan of always defaulting a value to the first value in the list. My philosophy for whether to default a value or not depends on a combination of:

1. What are the chances that I can guess the value correctly? If I can't guess it with a sufficiently high degree of confidence, then I don't default it to anything. For example, if one option is used 10% of the time and all the others are only used 5%, the "big" option isn't "big" enough for me to default to it.

2. What's the risk if the user doesn't notice that the value has been defaulted and blindly accepts my default? If this process was to delete information for a city, I wouldn't want to risk that the user blindly accepts my default city.