Data Validation Manipulation

[pyarb]

drop down selects
Hello,

I currently have a drop down box which list location names. The proble
is the location names are very generic (only part of the location info)
What I would like to do is have a drop down list actually list the ful
location name but populate the field with the generic name whe
selected. I could add another column but this file will be used a
import data and the extra field is not part of the extract layout.

Ex.

Generic name:T100

Full location name: Trumbull 100 (specific trumbull address)

User's would select trumbull 100 from the drop down list, but the nam
T100 would actually populate the field. I tried to reference th
generic range but keep on getting a circular reference error. Can thi
be done

 

[Debra Dalgleish]

You could do this with programming. There's a sample workbook on my web
site that shows a product name and code in the Data Validation dropdown
list. After an item is selected, the cell shows only the product name.
You may be able to adapt this to your workbook.

On the following page:

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

Under the data validation heading, look for 'Data Validation "Columns"

 

[pyarb]

Thanks, Debra!.

I unzipped your worksheet. However, when I click on the dropdown and
choose a product, the product and id remain, does not automatically
fill in just the product name. Also, I selected enable macros.

 

[Debra Dalgleish]

Perhaps events have been disabled. Does it work if you run the MyFix
macro that's also in the workbook?

 

[pyarb]

Running the macro, it still doesn't work. I've tried using your cod
for my workbook and it kind of works, but with a delayed response. I
I click on the drop down , choose my selection, it does't populate tha
cell with the corresponding name unless I pop back into the cell. If
leave the cell and then go back to the cell than it updates the cel
with the corresponding name

 

[Debra Dalgleish]

Does the code work correctly in the sample workbook that you downloaded?

 

[pyarb]

Hi Debra,

No, it doesn't work in your sample workbook that I downloaded. I used
the data validations columns sample workbook.

 

[Debra Dalgleish]

What version of Excel are you using? If it's Excel 97, the code won't
work, as explained here:

http://www.contextures.com/xlDataVal08.html#Change

 

[pyarb]

Yes, I am currently using excel 97

 

[pyarb]

Debra,

As I mentioned earlier, I've used your code for my workbook and it
kind of works, but with a delayed response. If I click on the drop down
, choose my selection, it does't populate that cell with the
corresponding name unless I pop back into the cell. If I leave the cell
and then go back to the cell than it updates the cell with the
corresponding name. Is there a way to have the event happen on leaving
the cell, once the cell loses focus can the update happen then.

 

[Debra Dalgleish]

Thanks for letting me know. Perhaps you can implement one of the
workarounds that's mentioned:

http://www.contextures.com/xlDataVal08.html#Change

 

[Debra Dalgleish]

When you go back to the cell later, then press Enter, you trigger the
Worksheet_Change event, and the code runs. There's no way to make this
happen in Excel 97, by selecting an item from the Data Validation list,
if the list source is on a worksheet.

You could select from the list, then press the F2 key, and press Enter.

Or, try a combobox overlay, as described here:

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