how to include more formulas in Data validation

nalaka

New Member
Joined
Jun 14, 2014
Messages
4
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

attachment.php

attachment.php
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Using your posted example, try this formula in your data validation (with K5:K12 selected and K5 as the active cell)
Code:
=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

New Member
Joined
Jun 14, 2014
Messages
4
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

Well-known Member
Joined
May 17, 2009
Messages
5,592
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
ABCDE
1Car Type>>a small carbigger carcd
2Seats14710
325811
436912
Sheet13


Excel 2007
JK
8Car TypeSeats
9bigger car5
10a small car3
11d11
Sheet13


Hope that helps.
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Type what characters? Where?
Can you also post a mock-up of the DV formula you're trying to build?
 

nalaka

New Member
Joined
Jun 14, 2014
Messages
4
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
ABCDE
1Car Type>>a small carbigger carcd
2Seats14710
325811
436912

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13


Excel 2007
JK
8Car TypeSeats
9bigger car5
10a small car3
11d11

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13



Hope that helps.

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

nalaka

New Member
Joined
Jun 14, 2014
Messages
4
i mean the length of the formula is limited within this data validation.
 
Last edited:

Forum statistics

Threads
1,171,568
Messages
5,876,222
Members
433,185
Latest member
ptr009

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top