Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Drop down to export the value to a linked cell

Posted on 2011-04-20
10
Medium Priority
?
276 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
0
Comment
Question by:labelonuk
  • 5
  • 5
10 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35433728
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
 

Author Comment

by:labelonuk
ID: 35439000
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35439030
You can't. If that's a necessity, you have to use controls on the sheet.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:labelonuk
ID: 35439082
Ok I might just have to use the Active X controls.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35439172
I'd use Forms controls if you can. They are generally more stable than ActiveX on worksheets.
0
 

Author Comment

by:labelonuk
ID: 35439176
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35439181
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
 

Author Comment

by:labelonuk
ID: 35439195
Thanks that worked a treat! Can I put this in a new sheet?

Thanks
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35439221
Yes - you can put it anywhere you like! :)
0
 

Author Comment

by:labelonuk
ID: 35439224
Thanks :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question