Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Choosing a Dependant List

Status
Not open for further replies.
wrightlefty

wrightlefty

Technical User
Feb 19, 2004
17
US
The spreadsheet function is to "build" a motor with different options available to the user. The first pulldown combo box has a list of series (1series, 2series, 3series, etc.). The user has the option to select a desired series, and the next pulldown box should have options available depending on what was selected in the first box. There are roughly 15 different options that will be dependant on what is first selected.

I have named each list relevant to the option (i.e. 1Shaft, 2Shaft, 3Shaft,etc.; 1frame, 2frame, 3frame, etc.). When the user chooses series list 2series, separate list boxes should then be filled with the info from 2shaft, 2frame, etc. I am able to populate cell C4 using validation by inserting the following function into the validate command:

=CHOOSE($C$3,1Frame,2Frame,3Frame,etc.)

where C3 is defined by: =MATCH(B3,Series,0)
where B3 is defined by: LinkedCell definition from Series
Combobox

All I need to do now is tie the validated list info from C4 into a listbox. The reason for this listbox vs. just using a cell with validation pulldown list is for user simplicity.

I am trying to avoid VB code and macros in the creation of this spreadsheet, but am open to their use if need be. Thanks for all of your help!
 
Sort by date Sort by votes


Hi,

There are several ways to accomplish this. I happen to like DYNAMIC Named Ranges using the OFFSET Function along with MATCH and COUNTIF.

How can I rename a table as it changes size faq68-1331

I would discourage you from naming individual ranges of the same kind like 1Shaft, 2Shaft, 3Shaft,etc.; 1frame, 2frame, 3frame. All your shaft-related data ought to be in a single list, like this---
[tt]
Blue Blueberry
Blue Grape
Red Apple
Red Cherry
Red Strawberry
Yellow Banana
Yellow Lemon
[/tt]
I Have Blue, Red, Yellow in my drop down
I can use MATCH to find where in the list the selection begins. I can use COUNTIF ro find how many there are in the list. And with those 2 pieces of data I can define the range in column 2, for my SECOND drop down, of the fruit relating to the fruit color selected.



Skip,
[sub]
[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue][/sub]
 
Upvote 0 Downvote
Skip,

I am not sure what you mean by "discourage you from naming individual ranges of the same kind like 1Shaft, 2Shaft, 3Shaft,etc.; 1frame, 2frame, 3frame"?

I have a worksheet with a list named Series populated as follows:

Series1
Series2
Series3
Series4
etc...

Depending on which series is chosen the user will then choose one option from the corresponding list like:

1Shaft
Keyway
Flat
Normal

2Shaft
Keyway
Flat
Normal
Long

3Shaft
Keyway
Normal

etc...

If the user chooses Series2, they will then have the option to choose (preferably with a combobox) only the information in 2Shaft (i.e. Keyway,Flat,Normal,Long).

Maybe what you responded earlier works for this, but I don't see how yet. Please explain further. Thanks.
 
Upvote 0 Downvote


I was strongly suggesting that you replace your LISTS with ONE LIST like this...
[tt]
Series Value
1Shaft Keyway
1Shaft Flat
1Shaft Normal
2Shaft Keyway
2Shaft Flat
2Shaft Normal
2Shaft Long
3Shaft Keyway
3Shaft Normal
[/tt]
This is the Professional Normalized approch.

Skip,
[sub]
[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue][/sub]
 
Upvote 0 Downvote
Skip,

OK. So I have one shaft list, one frame list, etc. How do I use the MATCH and COUNTIF functions to populate a combobox?
 
Upvote 0 Downvote

There are several ways to accomplish this. I happen to like DYNAMIC Named Ranges using the OFFSET Function along with MATCH and COUNTIF.

How can I rename a table as it changes size FAQ68-1331

I would discourage you from naming individual ranges of the same kind like 1Shaft, 2Shaft, 3Shaft,etc.; 1frame, 2frame, 3frame. All your shaft-related data ought to be in a single list, like this---

Blue Blueberry
Blue Grape
Red Apple
Red Cherry
Red Strawberry
Yellow Banana
Yellow Lemon

I Have Blue, Red, Yellow in my drop down
I can use MATCH to find where in the list the selection begins. I can use COUNTIF ro find how many there are in the list. And with those 2 pieces of data I can define the range in column 2, for my SECOND drop down, of the fruit relating to the fruit color selected.
Click to expand...

Skip,
[sub]
[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue][/sub]
 
Upvote 0 Downvote


Here it is by the numbers...


Sheet: List1
ListFillRange for dd1
[tt]
ShaftList
1Shaft
2Shaft
3Shaft
[/tt]
Sheet: List2
[tt]
Series Value
1Shaft Keyway
1Shaft Flat
1Shaft Normal
2Shaft Keyway
2Shaft Flat
2Shaft Normal
2Shaft Long
3Shaft Keyway
3Shaft Normal
[/tt]

User selects 2Shaft in dd1
[tt]
=MATCH(dd1Selection,Series,0) returns 4.

=COUNTIF(Series,dd1Selection) returns 4.
[/tt]
ListFillRange for dd2
[tt]
=OFFSET(List2!$A$1,MATCH(dd1Selection,Series,0),1,COUNTIF(Series,dd1Selection),1)
[/tt]

Skip,
[sub]
[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue][/sub]
 
Upvote 0 Downvote
Skip,

Thanks for trying to help. I don't think we're quite on the same page though and I don't know how to explain it any differently. I tryed the match, countif, and offset functions, but they still don't populate a combobox. Thanks again.
 
Upvote 0 Downvote


They sure do! I use this approch quite often.

It would bode well for you to carefully study the OFFSET function in Excel Help in order to understand its power for defining DYNAMIC ranges.

Did you follow what MATCH and COUNTIF contribute to the mix?

Skip,
[sub]
[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue][/sub]
 
Upvote 0 Downvote
Skip,

Since you use this approach often, is there anyway you could send a sample spreadsheet to demonstrate this? I have studied the OFFSET function, but it just isn't making sense to me.
 
Upvote 0 Downvote

What about it is not making sense?

Skip,
[sub]
[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue][/sub]
 
Upvote 0 Downvote
The part where the ListFillRange is populated from the offset command.
 
Upvote 0 Downvote


Define a Named Range in Insert/Name/Define.

use that name in the ListFillRange.

Skip,
[sub]
[glasses] [red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![tongue][/sub]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

N1GHTEYES
  • Locked
  • Question
Count areas within a range 1
Replies
14
Views
730
SkipVought
S
remeng
  • Question
Excel Pivot Table - Combine Values into a single row
Replies
15
Views
956
combo
combo
pendle666
  • Locked
  • Question
Change colour of cells based on dropdown list value in another - Excel professional plus 2016 1
Replies
5
Views
175
pendle666
pendle666
Flavescent
  • Locked
  • Question
Excel 365 - like a cut out figure
Replies
4
Views
189
SkipVought
S
Scott24x7
  • Locked
  • Question
Dynamic Unique List 2
Replies
10
Views
105
Scott24x7
Scott24x7

Part and Inventory Search

Sponsor

Back
Top