Display Pick From List Drop Down Arrow

[ruterej]

How do I display the Drop Down Arrow when using the Pick from List function (under Data - Validation)?
I have already setup the pick from list and that works fine but when I move the cursor away from the cell the drop down arrow that denotes Pick From List disappears. Please help.

 

[SydneyGeek]

Hi, welcome to the board!

That's the standard behaviour of validation lists. They look like normal cells until you select them, then the drop-down appears.
Maybe you could colour cells containing validation lists so users can recognise them.

Denis

 

[ruterej]

Denis, thanks for the response.
I have seen some excel spreadsheets where the drop down arrows are permanently displayed irrespective of the cursor position. Is this a different feature from data validation and how can I get mine to also be permanently displayed?

 

[Brian from Maui]

Denis, thanks for the response.
I have seen some excel spreadsheets where the drop down arrows are permanently displayed irrespective of the cursor position. Is this a different feature from data validation and how can I get mine to also be permanently displayed?

A similar problem here,

http://www.mrexcel.com/board2/viewtopic.php?t=181730&highlight=

 

[SydneyGeek]

To have the drop-down permanently displayed, you need to use a Combo box. There are 2 kinds -- one is found on the Forms toolbar (View | Toolbars | Forms). Use this one, and follow these instructions:

1. Drag a Combo box onto the spreadsheet -- don't worry where at this point. Once it's there, use the handles to resize it so you get a list area with a button at the right. If you just click on the forms toolbox and click on the worksheet, you will likely get a square with only a drop-down arrow. Just resize...

Note -- if you click outside the control at any point, you activate it. To go back to editing mode, right-click on it and Format Control.

2. On the Control tab are 2 input boxes. Input Range is for the list that will populate the combo. Click the range filder button, select the input cells, and hit Enter. Cell Link is the cell where you will store the output. Click the range finder again, select the output cell, and click Enter twice.

Try it out -- if it works, re-enter Edit mode (as above) and reposition / resize the control so it's over the output cell.

That's it. Trouble is, if you want to have this for 20 cells, it's a big job.

Denis

 

[RAM]

Try it out -- if it works, re-enter Edit mode (as above) and reposition / resize the control so it's over the output cell.

ruterej,

When you want to reposition/resize the control you can hold down the Ctrl key and left-click on the control. That will put the control in a "position-size" edit mode.

Edit:

That's it. Trouble is, if you want to have this for 20 cells, it's a big job.

Nah....not really. It's a god practice to remember for the next time. If I need to do that I would make one drop-down and then copy and paste that box all the times needed. This will make all drop-downs look exactly the same. It will go faster using the form later with 20 drop-downs than typing in each one. It also minimizes the risk for mistakes.

RAM