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 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13
Excel 2007
| J | K |
---|
8 | Car Type | Seats |
9 | bigger car | 5 |
10 | a small car | 3 |
11 | d | 11 |
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13
Hope that helps.