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.