We help IT Professionals succeed at work.

Excel Drop down to export the value to a linked cell

labelonuk
labelonuk asked
on
Medium Priority
284 Views
Last Modified: 2012-05-11
Hi All,

I am creating an order form which needs to have a drop down lists so they can only select the correct ones. I have created the drop down lists and got that to link to a cell which work but it exports the position number not the value they selected. I have sucessfully done this using the active x controls but i didnt want to use them as some version block them and they can be a pain.

Is it possible to use the form control drop down menus to export the value:

Ie RED then it exports that to the linked cell as RED.

I need to do this so when the form comes back we can automatically import the flat excel file into Access. So that why I need to link it to a cell on a new hidden sheet.

Any ideas?

Thanks
Adam
M-CO-ORDER-FORM.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi,

I have tried the data validation dropdowns but how can i get the drop down arrow to show all the time?

Thanks
Adam
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You can't. If that's a necessity, you have to use controls on the sheet.

Author

Commented:
Ok I might just have to use the Active X controls.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
I'd use Forms controls if you can. They are generally more stable than ActiveX on worksheets.

Author

Commented:
Yes I agree but i cant get that to export the value just the position. I am not that great at excel and not sure how to use the INDEX feature?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Let's say the foms combo gets its values from A1:A10 and is linked to B1. The formula would be just:
=INDEX(A1:A10,B1)

Author

Commented:
Thanks that worked a treat! Can I put this in a new sheet?

Thanks
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Yes - you can put it anywhere you like! :)

Author

Commented:
Thanks :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.