Dropdown Lists...ComboBoxes...Formulas

[Alec H]

Hi,

I have 2 lists on the same worksheet, I want to create 2 corresponding
dropdown lists (preferably in ComboBox format for user entry ease) in
another sheet in the workbook.

The 2nd list needs to be dependant and sorted based on the 1st list and
both lists need to remove duplications.

I have created a sample workbook based on the example provided on the
Contextures site and just input my data and changed a range size (see
attachment).

This appears to work to an extent.

My problems are 4 fold.

1 - How come my dropdowns dont operate fully on the lists? (What coding
change should I have made that I have missed)

2 - How do I stop the dropdowns showing duplicates?

3 - How do I then apply these lists to ComboBoxes (in
Data/Validation/Lists it lets me put in a formula, in ComboBox
Properties/ListFillRange it doesn't)

4 - After the lists are funtional (1, 2 and 3 above) the user chosen
results need to then be compatible with the following code (??1?? &
??2?? represent where the references to the list selections will go in
the code).


Code:
--------------------


=IF(??1??<>"",IF(??2??<>"",INDEX('Customer List'!$A$2:$HD$5000,MATCH(1,('Customer List'!$A$2:$A$5000='Lookup Sheet'!??1??)*('Customer List'!$B$2:$B$5000=??2??),0),MATCH('Lookup Sheet'!C8,'Customer List'!$A$1:$I$1,0)),""),"")


--------------------


All help to any of the above problems would be gratefully received.


+-------------------------------------------------------------------+
|Filename: Sample1.2.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4462 |
+-------------------------------------------------------------------+

 

[Ardus Petus]

I can't read your attachment:
http://www.excelforum.com/attachment.php?postid=4462

--
AP

Hi,

I have 2 lists on the same worksheet, I want to create 2 corresponding
dropdown lists (preferably in ComboBox format for user entry ease) in
another sheet in the workbook.

The 2nd list needs to be dependant and sorted based on the 1st list and
both lists need to remove duplications.

I have created a sample workbook based on the example provided on the
Contextures site and just input my data and changed a range size (see
attachment).

This appears to work to an extent.

My problems are 4 fold.

1 - How come my dropdowns dont operate fully on the lists? (What coding
change should I have made that I have missed)

2 - How do I stop the dropdowns showing duplicates?

3 - How do I then apply these lists to ComboBoxes (in
Data/Validation/Lists it lets me put in a formula, in ComboBox
Properties/ListFillRange it doesn't)

4 - After the lists are funtional (1, 2 and 3 above) the user chosen
results need to then be compatible with the following code (??1?? &
??2?? represent where the references to the list selections will go in
the code).


Code:

List'!$A$2:$HD$5000,MATCH(1,('Customer List'!$A$2:$A$5000='Lookup
Sheet'!??1??)*('Customer List'!$B$2:$B$5000=??2??),0),MATCH('Lookup
Sheet'!C8,'Customer List'!$A$1:$I$1,0)),""),"")

 

[Alec H]

I don't know what the problem is, I can access the zip ???

The file is basically the same as this one except I have a much greate
number of items in the lists.

http://www.contextures.com/DataValRegionCust.zip

Also I have duplications in both columns not just column A.

To explain the last comment, my 2 lists are;

Company - A Company Name

Branch - The town or city the branch is in.

It is quite possible (and does happen in the lists) that one Compan
will have multiple branches and it is also possible (and again doe
happen) that multiple Companies have branches in the same town.

Hope this helps clarifie