Unable to add to dropdown list

[NormVance]

hello all,

The problem I am facing can be best illustrated using the following example:
Dynamically Updating a Drop Down List that uses a dependent list.
I have a list of zones –I have two dropdowns: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

The first is ZonesLocality (8 choices) and second 8 Individual dropdown choices; for Individual dropdown choices I have LocalZone (20mi), LocalityWest, LocalityNorth, LocalityEast, LocalitySouth and three unused choices (sheet 1).

Then I have this Voucher worksheet (sheet 2), which will work like this: in cell "B18", there is a drop down list of ZoneLocality. If I select "any ZoneLocality", then in cell "B19 through B27" the Individual drop-down list, only "that zone locations" will dropdown for clicking.

Now comes the part that has me bewildered. Al need al of the Individual dropdown choices to have dynamic expandable. For example, the LocalZone (20mi), all chooses within 20 miles will show. So will each dropdown location as it lists is picked -- the problem comes when the location of destination is not on the list. I’ve set the list so when this happens the driver can add it. So far so good, however the next time the driver goes to this location he must re-add the location. I know how to set up a list so it will add items to the list as they are used. Or, a dependent list that will not add to the list. Can anyone show me how combined the two or how this can be done?

I did a search on threads and found some useful threads, however after 10 days no luck fixing the need.

I tried to use offset() function to make lists expandable, but then indirect() function would not work.

Any suggestions?

Norm<o></o>

 

[hiker95]

NormVance,

Welcome to the MrExcel forum.


See if one of these will help:

Dependent validation lists. Debra has a neat little tutorial here.
http://www.contextures.com/xlDataVal02.html

Data Validation > Drop-Down Lists - Dependent
http://www.bettersolutions.com/excel/EGH188/QE229212022.htm

Cascading queries
http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

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

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

 

[NormVance]

THank You for your input, however all the contacts so far have the same answer: You can eather have dependent list or have the ability to have the added item to the list saved and added to the list not both. I feel the may be a simple request but not simplly done.

Thank you so far for the help!!!

Norm