I have successfully implemented cascading dropdown lists, where the contents of the dropdown are filtered by the previous selection, using an adaptation of Contextures Offset/Match method of validation.

However at present I have to click in the cell to make the dropdown arrows visible at the right edge, then click on the arrows to open the drop box.

The application is to be used for inventory, standing up. Preision mousing is a pain.

I would like the dropdown to open immediately on clicking in the cell. Is this possible?


Will a double-click work?

This solution requires two clicks, one to activate the cell and one to drop the list down. If the cell is already active, only one click is required.

I don't know about OSX Mountain Lion, but this works in Windows 7, Excel 2010.

  • Highlight all cells in the sheet. (You can do so easily by clicking above and to the left of A1.

Select all cells

  • Go to the Ribbon > Home > Cells group > Format > Protection > and unlock cells. (Cells are locked by default.)

Unlock all cells

  • Lastly, go to the Ribbon > Review > Changes group > Protect Sheet > and protect the sheet. (You don't even need to use a password. Just click OK.)

Protect sheet

Now that the sheet is protected, you should be able to double-click the cell with data validation and that will drop down the list.

  • Hot damn! That works. A double click now opens the cell. Points to you my friend. – Sherwood Botsford Feb 14 '14 at 5:18
  • Can you explain why? – Sherwood Botsford Feb 14 '14 at 5:18
  • Or not. A double click make the ribbon appear, but when I make a selection, I get an error that it's protected. Rubber chickens. Walking round windershins, chanting ancient Pictish curses... – Sherwood Botsford Feb 14 '14 at 5:22
  • @SherwoodBotsford Ah! I knew I missed something. Unlock the cell with data validation. I thought it needed to be locked, but it doesn't. I'm going to edit my answer too. – ARich Feb 14 '14 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.