Selecting from a list in excel 1

[kdeans]

Here's what I would like to do. I want to select a certain model number (drop down validation list) which would then let me select from a list of parts for that particular model. I can accomplish this rather crudely and I am thinking that someone must have a better method. I use a table with all the model numbers and parts numbers associated with each. When I select the model from the list, it copies all the part numbers to another list area using a series of choose and match functions. This was fine until I surpassed 30 model numbers. (It seems that 29 columns is the most that will work with these functions) Following is one of the equations that worked up to 29 items: (bh301 is one column too many)
=IF(CHOOSE((MATCH
($W$301,$X$301:$X$330,0)),AF301,AG301,AH301,AI301,AJ301,AK301,AL301,AM301,A
N301,AO301,AP301,AQ301,AR301,AS301,AT301,AU301,AV301,AW301,AX301,AY301,AZ30
1,BA301,BB301,BC301,BD301,BE301,BF301,BG301,BH301)=0,"",CHOOSE((MATCH
($W$301,$X$301:$X$330,0)),AF301,AG301,AH301,AI301,AJ301,AK301,AL301,AM301,A
N301,AO301,AP301,AQ301,AR301,AS301,AT301,AU301,AV301,AW301,AX301,AY301,AZ30
1,BA301,BB301,BC301,BD301,BE301,BF301,BG301,BH301))

 

[PeterMoran]

Hi,

You might like to checkout this url under Data Validation for setting up dependant lists which can automatically update:

http://www.contextures.com/tiptech.html

Good Luck!
Peter Moran

 

[01Patrik]

How many equations do you need? Myself I like simple solutions:

Put the following in a cell by itself (here A1).

=MATCH($W$301,$X$301:$X$330,0)

Then use this instead of your function (here in B1):

=IF(A1>29,"",if(CHOOSE
(A1),AF301,AG301,AH301,AI301,AJ301,AK301,AL301,AM301,AN301,AO301,AP301,AQ30
1,AR301,AS301,AT301,AU301,AV301,AW301,AX301,AY301,AZ301,BA301,BB301,BC301,B
D301,BE301,BF301,BG301,BH301)=0,"",CHOOSE
(A1),AF301,AG301,AH301,AI301,AJ301,AK301,AL301,AM301,AN301,AO301,AP301,AQ30
1,AR301,AS301,AT301,AU301,AV301,AW301,AX301,AY301,AZ301,BA301,BB301,BC301,B
D301,BE301,BF301,BG301,BH301))

Then create another function (here in C1):

=IF(A1<30,"",if(CHOOSE(A1-
29),AF301,AG301,AH301,AI301,AJ301,AK301,AL301,AM301,AN301,AO301,AP301,AQ301
,AR301,AS301,AT301,AU301,AV301,AW301,AX301,AY301,AZ301,BA301,BB301,BC301,BD
301,BE301,BF301,BG301,BH301)=0,"",CHOOSE(A1-
29),AF301,AG301,AH301,AI301,AJ301,AK301,AL301,AM301,AN301,AO301,AP301,AQ301
,AR301,AS301,AT301,AU301,AV301,AW301,AX301,AY301,AZ301,BA301,BB301,BC301,BD
301,BE301,BF301,BG301,BH301))

Last, the 'output cell':

=if(B1="",C1,B1)


Of course this can be multiplied depending on the number of equations you have.


// Patrik

 

[jdhilljr]

Here is an example using Vlookup with a combobox. There are no limitations as with 'choose' and 'if'.

http://www.procollectortips.com/lookup.xls

Jim

 

[01Patrik]

Yes, of course, VLOOKUP is better:

=vlookup(AF301,AF301:BH301,MATCH($W$301,$X$301:$X$330,0),0)

where BH301 is the presumed end column... expand to your needs.

Thx Jdhilljr, I'm a bit slow today...

// Patrik

 

[kdeans]

I used an hlookup to accomplish the task with probably unlimited expansionablity as follows: =IF($W$301>0,HLOOKUP($W$301,Parts_List,2,FALSE),""). On each succesive line I increased the offset by one until well beyond the number of parts for any of the models.
Thanks for the help. I should have realized this earlier but you sometimes get thinking down one avenue and it's hard to deviate.

 

[klmorgan]

Not sure if I understand what you want but it seems to me simpler to:

Set a Validation in Say A2 that uses a list having:

Model 1
Model 2
Model 3

Then have a list named Model 1 that holds parts for model 1

ie. Brake Pad
Starter
Disc

and likewise for the other models

then in say cell d2 set validation source as =INDIRECT(A2)

This will then set the choices in D2 conditional on the choice made in a2

You can then cascade this down to further levels if you want

Regards

Keith

P.S. if this is not clear I will email you an example

 

[fkegley]

I would begin by building list of model numbers, and each different set of part numbers, assigning each list appropriate range names.

I would then develop a custom form with a combo box and a list box on it to allow the user to select the model and part numbers. Set the Row Source property of the combo box to the model number range name. On the click event of the combo box, I would attach code to change the Row Source property of the list box to the range name that is appropriate for the model number. Once that has been done, I would use code to place the model number and selected part number(s) to the appropriate place in the spreadsheet.

You'll need a macro to show the form. You'll also need to know which cells you want to put the selected items into. This won't be any harder than what you have already been doing.

Frank kegley
fkegley@hotmail.com

 

[kdeans]

Keith,
That is a beautiful thing. Your solution is perfect. A very simple way to have a list dependant on a list and the ability to expand. Thanx.

 

[klmorgan]

I learned that tip on Tek-Tips!

Always keep it simple if you can

Glad to be of help

Keith