Auto update a list I can select from

You need to create a dynamic named range for that purpose. So assuming that you have cities listed in col. A with A1 as header (City), go to Formulas Tab in the menu bar --> Define Name --> In the Name box write City --> then in the Refers to: box type the formula given below and click OK.

Please Login or Register  to view this content.

Now go to Data tab --> Data Validation --> Allow: --> List --> click in the Source box --> Press F3 --> and select City named range from the available named ranges --> OK

Hope that helps.

** In the formula above it is assumed that your sheet name is Sheet1 (default), so if you have different sheet name, you need to change it in the formula.

Hi,

First you will need a Dynamic Named Range in Column A. This will grow (or shrink) depending on how many rows of data you have in column A.
Read about DNRs at http://www.bettersolutions.com/excel...G820716330.htm

Then you can't have Filters or Validation so I'd lean toward the Search or Find features and VBA.

Record a macro that selects column A and then runs the Find (or Search) command from VBA. Then hang this macro behind the Sheet in an Event procedure of On_Change.

I hate these problems with so many constraints... See if you can work with the above.

See the attachment.

You're welcome. Thanks for the feedback.

Using your example workbook, I inserted a table for the cities. In column B starting in B1 and going down the column a ways is DV that will expand as you add cities to the table.