Excel Drop down to export the value to a linked cell

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
labelonukAsked:
Who is Participating?
 
Rory ArchibaldCommented:
I suggest you use Data Validation dropdowns and then link the hidden sheet cells as require using straight cell link formulas (=Sheet1!A1)
Otherwise, use the forms ones to link to cells on the same sheet as the controls and then use INDEX formulas to get the right value from the list range using the position returned by the control.
0
 
labelonukAuthor 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
0
 
Rory ArchibaldCommented:
You can't. If that's a necessity, you have to use controls on the sheet.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
labelonukAuthor Commented:
Ok I might just have to use the Active X controls.
0
 
Rory ArchibaldCommented:
I'd use Forms controls if you can. They are generally more stable than ActiveX on worksheets.
0
 
labelonukAuthor 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?
0
 
Rory ArchibaldCommented:
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)
0
 
labelonukAuthor Commented:
Thanks that worked a treat! Can I put this in a new sheet?

Thanks
0
 
Rory ArchibaldCommented:
Yes - you can put it anywhere you like! :)
0
 
labelonukAuthor Commented:
Thanks :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.