drop down selects
Hello,

I currently have a drop down box which list location names. The problem
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 full
location name but populate the field with the generic name when
selected. I could add another column but this file will be used as
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 name
T100 would actually populate the field. I tried to reference the
generic range but keep on getting a circular reference error. Can this
be done?


--
pyarb
------------------------------------------------------------------------
pyarb's Profile: http://www.excelforum.com/member.php...o&userid=26803
View this thread: http://www.excelforum.com/showthread...hreadid=400570

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 wrote:
drop down selects
Hello,

I currently have a drop down box which list location names. The problem
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 full
location name but populate the field with the generic name when
selected. I could add another column but this file will be used as
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 name
T100 would actually populate the field. I tried to reference the
generic range but keep on getting a circular reference error. Can this
be done?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.


--
pyarb
------------------------------------------------------------------------
pyarb's Profile: http://www.excelforum.com/member.php...o&userid=26803
View this thread: http://www.excelforum.com/showthread...hreadid=400570

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

pyarb wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Running the macro, it still doesn't work. I've tried using 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.


--
pyarb
------------------------------------------------------------------------
pyarb's Profile: http://www.excelforum.com/member.php...o&userid=26803
View this thread: http://www.excelforum.com/showthread...hreadid=400570

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

pyarb wrote:
Running the macro, it still doesn't work. I've tried using 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.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Hi Debra,

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


--
pyarb
------------------------------------------------------------------------
pyarb's Profile: http://www.excelforum.com/member.php...o&userid=26803
View this thread: http://www.excelforum.com/showthread...hreadid=400570

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

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

pyarb wrote:
Hi Debra,

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




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Yes, I am currently using excel 97.


--
pyarb
------------------------------------------------------------------------
pyarb's Profile: http://www.excelforum.com/member.php...o&userid=26803
View this thread: http://www.excelforum.com/showthread...hreadid=400570

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.


--
pyarb
------------------------------------------------------------------------
pyarb's Profile: http://www.excelforum.com/member.php...o&userid=26803
View this thread: http://www.excelforum.com/showthread...hreadid=400570

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

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

pyarb wrote:
Yes, I am currently using excel 97.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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 he

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

pyarb wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html