how to include more formulas in Data validation

[nalaka]

i have written a formula for "k"(No. of seats) column in data validation to change the displaying drop-down data list based on value of the "J"(car type) column. i used "if" in data validation to do this. but i cannot write more repetition of "if" inside of the data validation. data validation dialog box do not allow to do this. so i cannot increase "No. of seats" data.

=IF(J9="a",$G$3:$G$5,IF(J9="b",$G$7:$G$9,IF(J9="c",$E$13:$E$16,0)))
data
please help me to do in anther way. even by using macros

 

[Ron Coderre]

Using your posted example, try this formula in your data validation (with K5:K12 selected and K5 as the active cell)

=CHOOSE(CEILING(SEARCH("_"&J5&"_","_a_b_c__")/2,1),$G$3:$G$5,$G$7:$G$9,$G$11:$G$13)

Note: You might want to include some VBA code to clear the Col_K selection when the Col_J value changes.

Does that help?

 

[nalaka]

thank you so much for helping.. :D

actually i want to extend this to a to l. but the problem is it does not allow to type characters withing data validation. how could i fix this?
thanks again

 

[Snakehips]

nalaka,

Welcome to MrExcel.

Maybe you can set out your Car seat data as follows.
The data validation range for the list of cars will then be $B$1:$E$1 and would increase in columns with any increase in number of cars.

Then make each of the lists of car seats below the header row and create a named range for each car.
The easy way to do that with the example data is to select range B1:E4 and in the Formula tab of the ribbon select >> Select names from selection from the Defines names section.
Tick Create Names from top row of selection >> Ok NB If there are spaces in the car type text the name will be created with Underscores replacing the spaces.

Then for the data validation of J9:J?? select your range J9:J?? >> Data Validation >> List >> and Select the headers B:E or beyond.
Data validation for K9:K??. Select your range K9:K?? >> Data Validation >> List and in the list Source input box type =INDIRECT(SUBSTITUTE($J9," ","_"))
The Substitue part of that formula puts Underscore instead of spaces, where spaces exist, so as to match the Named Ranges.
Job Done. Because the Car Type selected in J will be equivalent to the named range that holds that car's seat numbers.

Excel 2007
	A	B	C	D	E
1	Car Type>>	a small car	bigger car	c	d
2	Seats	1	4	7	10
3		2	5	8	11
4		3	6	9	12
Sheet13

Excel 2007
	J	K
8	Car Type	Seats
9	bigger car	5
10	a small car	3
11	d	11
Sheet13

Hope that helps.

 

[Ron Coderre]

Type what characters? Where?
Can you also post a mock-up of the DV formula you're trying to build?

 

[nalaka]

nalaka,

Welcome to MrExcel.

Maybe you can set out your Car seat data as follows.
The data validation range for the list of cars will then be $B$1:$E$1 and would increase in columns with any increase in number of cars.

Then make each of the lists of car seats below the header row and create a named range for each car.
The easy way to do that with the example data is to select range B1:E4 and in the Formula tab of the ribbon select >> Select names from selection from the Defines names section.
Tick Create Names from top row of selection >> Ok NB If there are spaces in the car type text the name will be created with Underscores replacing the spaces.

Then for the data validation of J9:J?? select your range J9:J?? >> Data Validation >> List >> and Select the headers B:E or beyond.
Data validation for K9:K??. Select your range K9:K?? >> Data Validation >> List and in the list Source input box type =INDIRECT(SUBSTITUTE($J9," ","_"))
The Substitue part of that formula puts Underscore instead of spaces, where spaces exist, so as to match the Named Ranges.
Job Done. Because the Car Type selected in J will be equivalent to the named range that holds that car's seat numbers.

Excel 2007

	A	B	C	D	E
Car Type>>	a small car	bigger car	c	d
Seats

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]

</tbody>

Sheet13​

Excel 2007

	J	K
Car Type	Seats
bigger car
a small car
d

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]11[/TD]

</tbody>

Sheet13​

Hope that helps.

i should grateful to you help very much. thanks its working

 

[nalaka]

i mean the length of the formula is limited within this data validation.